SQL select top 2 customers with most purchases for each item and display their most recent purchase-CodePudding

I would like to identify the top 2 users by purchase volume and when their last order was, for each item, from this dataset

customer_id    item      created_at    revenue
1              apple       3/3/2022        23
2              pear       3/18/2022        21
3              apple      3/18/2022        76
4              banana     3/18/2022        62
1              apple      3/28/2022        33
3              peanut     3/29/2022        62
1              banana      3/7/2022        52
4              peanut     3/13/2022        17
1              peanut     3/30/2022        29
2              banana     3/17/2022        12
3              peanut     3/31/2022        26
2              apple       3/4/2022        12

purchase volume can be inferred from the revenue. Please help!

CodePudding user response：

Quick solution ( not validated for syntax )

select t.customer_id , t.item  , max(created_at) as last_order_dt
from
 table_name t JOIN 
( 
select TOP 2 customer_id, sum(revenue)
from table_name 
group by customer_id
order by sum(revenue) desc 
) a 
t.customer_id = a.customer_id