Lessons › SQL › Go further in SQL: Business questions

SQL lesson

What's the best-selling product in each store?

What's the best-selling product in each store? Two things have to be worked out first: each product's total in each store, then a ranking that starts again for every store. PARTITION BY store makes the ranking start again.

Example

Total, then rank inside each group

WITH totals AS (
  SELECT store, product, SUM(revenue) AS revenue
  FROM orders
  GROUP BY store, product
), ranked AS (
  SELECT store,
         product,
         revenue,
         RANK() OVER (PARTITION BY store ORDER BY revenue DESC) AS rnk
  FROM totals
)
SELECT store, product, revenue
FROM ranked
WHERE rnk = 1

Result:

Step by step

1. Each store's top product.

   WITH totals AS (SELECT store, product, SUM(revenue) AS revenue FROM orders GROUP BY store, product), ranked AS (SELECT store, product, revenue, RANK() OVER (PARTITION BY store ORDER BY revenue DESC) AS rnk FROM totals) SELECT store, product, revenue FROM ranked WHERE rnk = 1

2. No join.

   WITH totals AS (SELECT store, product, SUM(revenue) AS revenue FROM orders GROUP BY store, product), ranked AS (SELECT store, product, revenue, RANK() OVER (PARTITION BY store ORDER BY revenue DESC) AS rnk FROM totals) SELECT store, product, revenue FROM ranked WHERE rnk = 1

3. WHERE rnk = 1, at the end.

   WITH totals AS (SELECT store, product, SUM(revenue) AS revenue FROM orders GROUP BY store, product), ranked AS (SELECT store, product, revenue, RANK() OVER (PARTITION BY store ORDER BY revenue DESC) AS rnk FROM totals) SELECT store, product, revenue FROM ranked WHERE rnk = 1

4. Two CTEs: totals, then ranked.

   WITH totals AS (SELECT store, product, SUM(revenue) AS revenue FROM orders GROUP BY store, product), ranked AS (SELECT store, product, revenue, RANK() OVER (PARTITION BY store ORDER BY revenue DESC) AS rnk FROM totals) SELECT store, product, revenue FROM ranked WHERE rnk = 1

Practice

Try each one in your head first, then open the answer.

WITH totals AS (SELECT store, product, SUM(revenue) AS revenue FROM orders GROUP BY store, product), ranked AS (SELECT store, product, revenue, RANK() OVER (PARTITION BY store ORDER BY revenue DESC) AS rnk FROM totals) SELECT store, product, revenue FROM ranked WHERE rnk = 1

WITH ranked AS (SELECT store, order_id, revenue, RANK() OVER (PARTITION BY store ORDER BY revenue DESC) AS rnk FROM orders) SELECT store, order_id, revenue FROM ranked WHERE rnk = 1

WITH ranked AS (SELECT customer_id, order_id, revenue, RANK() OVER (PARTITION BY customer_id ORDER BY revenue DESC) AS rnk FROM orders) SELECT customer_id, order_id, revenue FROM ranked WHERE rnk = 1

WITH totals AS (SELECT product, store, SUM(revenue) AS revenue FROM orders GROUP BY product, store), ranked AS (SELECT product, store, revenue, RANK() OVER (PARTITION BY product ORDER BY revenue DESC) AS rnk FROM totals) SELECT product, store, revenue FROM ranked WHERE rnk = 1

WITH totals AS (SELECT store, product, SUM(revenue) AS revenue FROM orders GROUP BY store, product), ranked AS (SELECT store, product, revenue, RANK() OVER (PARTITION BY store ORDER BY revenue DESC) AS rnk FROM totals) SELECT store, product, revenue FROM ranked WHERE rnk <= 2