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
but I need following 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
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;