Use 1 SQL query to join 3 tables and find the category of products that generates the most revenue for each customer segment

Question

I am using SQLite3 for this following query.

I have a table called "products" that looks like this:

I have a table called "transactions" that looks like this:

I have a table called "segments" that looks like this:

For each active segment, I want to find the category that produces the highest revenue.

I think that I know how to do this in 3 different queries.

create table table1 as
SELECT s.seg_name, p.category, t.item_qty * t.item_price as revenue
from segments s
JOIN
transactions t
on s.cust_id = t.cust_id
JOIN products p
on p.prod_id = t.prod_id
where s.active_flag = 'Y'
order by s.seg_name, p.category
;

create table table2 as
select seg_name, category, sum(revenue) as revenue
from table1
group by seg_name, category;

select seg_name, category, max(revenue) as revenue
from table2
group by seg_name;

How can I do it in 1 query?

Answer 1

here is one way :

select seg_name,category,revenue
from (
select
    s.seg_name,
    p.category,
    sum(t.item_qty * t.item_price) as revenue,
    rank() over (partition by seg_name order by sum(t.item_qty * t.item_price) desc) rn 
from segments s
join transactions t on s.cust_id = t.cust_id
join products p on p.prod_id = t.prod_id
where s.active_flag = 'Y'
group by seg_name, p.category
) t where rn = 1