问题

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 the 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 ;

but it is giving the following output:

but I need the following output:

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

In this query, as you can see from the expected image, I want to print the maximum item_bought_count of a product for each customer. I sorted all of this using dense rank. Now, I just have to select rows where dense rank = 1 for each customer. I cannot figure out how to do that. Any suggestions on how to do that?

One more thing: I am writing the query in MySQL Workbench, so it is quite different from SQL Server solutions and help available on the internet.

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 ;

答案1

得分: 0

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 子查询
WHERE purchase_rank = 1;

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;