Problem Description –
Write an SQL query to find the most frequently ordered product(s) for each customer.
The result table should have the product_id and product_name for each customer_id who ordered at least one order.
Return the result table in any order.
The query result format is in the following example.
Problem Link – Most Frequently Ordered
Difficulty Level – Medium
Solution –
SELECT
customer_id,
product_id,
product_name
FROM (
SELECT
o.customer_id,
o.product_id,
p.product_name,
DENSE_RANK() OVER(PARTITION BY o.customer_id ORDER BY COUNT(*) DESC) as rnk
FROM Orders as o JOIN Products as p
ON o.product_id = p.product_id
GROUP BY 1, 2, 3
ORDER BY 1
) x
WHERE rnk = 1