Problem Description –
Write an SQL query to find the most frequently ordered product(s) for each customer.
The result table should have the
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
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