๐ For each user, find their most frequently purchased product
(If tie โ return all tied products)
Table: Orders
user_id | product_id
๐ก Query:
WITH freq AS (
SELECT user_id,
product_id,
COUNT(*) AS cnt
FROM Orders
GROUP BY user_id, product_id
),
ranked AS (
SELECT *,
RANK() OVER (PARTITION BY user_id ORDER BY cnt DESC) AS rnk
FROM freq
)
SELECT user_id, product_id, cnt
FROM ranked
WHERE rnk = 1;
๐ฏ Why this matters:
โ Tests aggregation + ranking
โ Handles tie cases
โ Common in real-world analytics
โก Pro Tip:
โ Aggregate first, then rank
โ Use
RANK() to include tiesโค๏ธ React for more questions