how to print max (item_bought_count)?

May 17, 2023

I was solving question 5 of case-study-1 of 8-week-sql-challenge by Danny. I am stuck at this point. I have written following code

select
  customer_id,
  product_name,
  count(sales.product_id) as 'item_bought_count',
  dense_rank() over(partition by customer_id order by count(sales.product_id) desc) as 'purchase_rank'
from sales
join menu on sales.product_id = menu.product_id
group by customer_id, sales.product_id ;

and it is giving following output

My_Output

but I need following output

Correct_Output

the Question : Which item was the most popular for each customer?

In this query as you can see expected image, I want to print max item_bought_count of product for each customer.I sorted all this using dense rank. Now i just have to take out rows having dense rank =1 for each customer. I cannot figure how to do that. Any suggestions how to do that ?

one thing more: I am writing query in mysql workbench, so it is quite different from sql server solutions and help available on intenet

>Solution :

SELECT customer_id, product_name, item_bought_count
FROM (
    SELECT 
        customer_id,
        product_name,
        COUNT(sales.product_id) AS item_bought_count,
        DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY COUNT(sales.product_id) DESC) AS purchase_rank
    FROM sales
    JOIN menu ON sales.product_id = menu.product_id
    GROUP BY customer_id, sales.product_id
) AS subquery
WHERE purchase_rank = 1;