Top Sellers Query
Problem
Given these tables, return the top 3 SKUs by revenue in each category over the last 30 days, counting only delivered orders. Revenue is qty * unit_price at the time of the order.
products(sku PK, category)
orders(id PK, customer_id, created_at, status) -- status: placed | delivered | cancelled
order_items(order_id FK, sku FK, qty, unit_price)
Output columns: category, sku, revenue, sorted by category then revenue descending. Ties may return more than 3 rows; tell me how you would change that if the product team says strictly 3.
Examples
Example 1
orders: 1 delivered 2026-08-20 | 2 delivered 2026-08-25 | 3 delivered 2026-06-01 | 4 cancelled 2026-08-28
order_items: (1,A1,1,500) (1,A2,2,300) (2,A3,1,900) (2,A1,1,500) (2,B1,3,50) (3,A4,5,100) (4,A4,9,100)
products: A1..A4 phones, B1 books
As of 2026-09-01 → phones: A1 1000, A3 900, A2 600 and books: B1 150. A4 is excluded: one order is too old, the other is cancelled.
Example 2 — a category with no delivered orders in the window returns no rows (not a row with NULL).
Constraints
order_itemshas~200Mrows;ordershas~50M. Say which indexes make this fast.- The "last 30 days" must be relative to
CURRENT_DATE; parameterise it. - Postgres syntax preferred; MySQL 8 accepted.
What they look for
A CTE that aggregates revenue per (category, sku), then DENSE_RANK() (or ROW_NUMBER() if strictly 3) partitioned by category, then a filter on rank. Joining orders before aggregating so the status and date filter cut early. Index on orders(status, created_at) and order_items(order_id). Candidates who write a correlated subquery for the top-N get asked to explain the cost.