-2

There is a table like this, how can I get customer_id, amount_spent, top_item out of it?

amount_spent the amount spent on all items by this customer

top_item, which displays the name of the item for which the customer has spent the most money

Table

I have tried the following query, however I cannot output the top_1 item with it


select customer_id, sum(item_number * item_price) as amount_spent_1m 
from temp
group by customer_id

Check the demo here.

Oleg Romanov
  • 7
  • 3

2 Answers2

1

You can achieve it as below :

select customer_id,  sum(item_number * item_price) as amount_spent_1m, 
item_name as top_item_1m
from temp
group by customer_id, item_name
order by amount_spent_1m desc;

It gives me the following result :

enter image description here

Tushar
  • 3,527
  • 9
  • 27
  • 49
  • Yes but, need to output one line for the customer, with the total amount spent and the item he/she spent the most on. But here the customer is output twice – Oleg Romanov Jan 23 '23 at 16:26
  • OK, I got your question now. I will update the answer in a while – Tushar Jan 23 '23 at 16:29
  • 1
    @Avidan You should be able to do it with window functions. Or turn this query into a CTE, then use one of the techniques at https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql?rq=1 to get the top row for each customer. – Barmar Jan 23 '23 at 16:36
1

I am sure there is a way to do this with one less step but my brain is not seeing it right now -

SELECT customer_id, amount_spent, item_name AS top_item
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY item_total DESC) rn,
    SUM(item_total) OVER (PARTITION BY customer_id) amount_spent
  FROM (
    SELECT customer_id, item_id, item_name, SUM(item_price * item_number) item_total
    FROM table1
    GROUP BY customer_id, item_id, item_name
  ) t1
) t2
WHERE rn = 1

db<>fiddle

user1191247
  • 10,808
  • 2
  • 22
  • 32