# Kings of the Calendar > Every month has a winner. Canonical URL: Domain: SQL · Difficulty: hard · Seniority: L4 · Asked in: eBay ## Problem We publish a monthly best-sellers leaderboard for an online marketplace, where a product's standing in a calendar month is set by its total units sold that month, and we only feature products that are currently in stock. For every month, surface the products holding the top three standings, along with their total units sold and standing. ## Tables ### transactions - transaction_id INTEGER - user_id INTEGER - product_id INTEGER - quantity INTEGER - total_amount REAL - transaction_date TEXT Sample rows (10 of 200): | transaction_id | user_id | product_id | quantity | total_amount | transaction_date | | --- | --- | --- | --- | --- | --- | | 1067 | 197 | 1001 | 2 | 23.46 | 2026-02-02 | | 1134 | 294 | 1050 | 3 | 36.93 | 2026-03-03 | | 1201 | 391 | 1099 | 4 | 50.4 | 2026-04-04 | | 1268 | 488 | 1148 | 5 | 63.87 | 2026-05-05 | | 1335 | 585 | 1197 | 0 | 0 | 2026-06-06 | | 1402 | 682 | 1246 | 2 | 90.81 | 2026-07-07 | | 1469 | 779 | 1295 | 3 | 104.28 | 2026-08-08 | | 1536 | 876 | 1344 | 4 | 117.75 | 2026-09-09 | | 1603 | 973 | 1393 | 5 | -45 | 2026-01-10 | | 1670 | 1070 | 1442 | 1 | 144.69 | 2026-02-11 | ### products - product_id INTEGER - product_name TEXT - category TEXT - price REAL - rating REAL - in_stock INTEGER Sample rows (10 of 200): | product_id | product_name | category | price | rating | in_stock | | --- | --- | --- | --- | --- | --- | | 1001 | Basic Device 1X | Books | 17.52 | 2.7 | 1 | | 1050 | Deluxe Bundle 2X | Clothing | 25.05 | 4.4 | 1 | | 1099 | Pro Unit 3X | Home & Kitchen | 32.58 | 2.1 | 1 | | 1148 | Ultra Tool 4X | Collectibles | 40.11 | 3.8 | 1 | | 1197 | Essential Set 5X | Toys | 47.64 | 1.5 | 1 | | 1246 | Classic System 6X | Beauty | 0 | 3.2 | 1 | | 1295 | Smart Gadget 7X | Automotive | 62.7 | 4.9 | 0 | | 1344 | Eco Kit 8X | Garden | 70.23 | 2.6 | 1 | | 1393 | Turbo Pack 9X | Music | 77.76 | 4.3 | 0 | | 1442 | Premium Widget 10X | Electronics | 85.29 | 2 | 1 | ## Expected output First 5 of 99 rows: | month | product_name | total_quantity | rnk | | --- | --- | --- | --- | | 2022-01 | Basic Device 61X | 2 | 1 | | 2023-01 | Smart Gadget 97X | 3 | 1 | | 2024-01 | Pro Unit 73X | 4 | 1 | | 2025-02 | Ultra Tool 74X | 5 | 1 | | 2026-04 | Premium Widget 40X | 1 | 3 | ## Worked solution and explanation ### What this really tests Strip the leaderboard costume and this is a per-partition top-N with tie preservation. The month is a grouping key you derive from `transaction_date`; inside each month you total units per product with `SUM(quantity)`, then assign a standing. The trap is the standing function: reach for `ROW_NUMBER` or a per-month `LIMIT` and you silently drop products tied on units, so a month with two products tied for third returns one and the interviewer never sees the other. `DENSE_RANK` holds every tied product at the same standing with no gaps, and because you cannot filter a window result in the same SELECT's `WHERE`, the standing has to live in a CTE you filter afterward. The `in_stock = 1` cut also belongs in that inner query, before the aggregation, so out-of-stock products never enter the totals. > **Ties keep their place with `DENSE_RANK`** > > The ask says "top three" and the sample shows equal totals sharing a place. That combination points at `DENSE_RANK()`, not `ROW_NUMBER()` or `LIMIT`: it gives tied values the same standing with no gaps, so a cutoff keeps every product that earned the spot. > > 1. Spot the shared-standing language > 2. Use `DENSE_RANK()` over `ROW_NUMBER()` > 3. Filter on the standing in a wrapping CTE --- ### Break down the requirements #### Step 1: Join and filter to stocked products Inner-join `transactions` to `products` on `product_id` so you can read `product_name` and `in_stock`, then keep only rows where `in_stock = 1`. Doing the filter here means out-of-stock products never reach `SUM(quantity)`. #### Step 2: Total units per product per month `GROUP BY STRFTIME('%Y-%m', a.transaction_date), b.product_name` with `SUM(a.quantity)` computes units sold at the month-and-product grain before any ranking happens. #### Step 3: Assign a per-month standing with `DENSE_RANK` `DENSE_RANK() OVER (PARTITION BY STRFTIME('%Y-%m', a.transaction_date) ORDER BY SUM(a.quantity) DESC)` restarts the standing inside each month and lets tied totals share a number without leaving a gap. #### Step 4: Cut to the top three Wrap the standings in a CTE and filter `WHERE rnk <= 3` in the outer query. You have to filter outside the window's SELECT, and the `<=` keeps every tied product on the third rung rather than an arbitrary one. --- ### The solution **Per-month top three with shared standings** ```sql WITH ranked AS ( SELECT STRFTIME('%Y-%m', a.transaction_date) AS month, b.product_name, SUM(a.quantity) AS total_quantity, DENSE_RANK() OVER (PARTITION BY STRFTIME('%Y-%m', a.transaction_date) ORDER BY SUM(a.quantity) DESC) AS rnk FROM transactions a JOIN products b ON a.product_id = b.product_id WHERE b.in_stock = 1 GROUP BY STRFTIME('%Y-%m', a.transaction_date), b.product_name ) SELECT month, product_name, total_quantity, rnk FROM ranked WHERE rnk <= 3 ``` > **One pass, not one scan per month** > > The fact table has 300M rows (77 GB). The whole job is one grouped pass: join to the 100k-row `products` dimension, drop the out-of-stock rows, then aggregate to one row per month-and-product. The `DENSE_RANK` runs on that already-reduced set, not the raw 300M, and a single pass beats any per-month self-join, which would re-scan the fact table once for every calendar month. > **The window-function choice is the tell** > > Whether the candidate picks the standing function on purpose: `DENSE_RANK` for shared, gap-free places versus `ROW_NUMBER` for a strict order, and can say why the cutoff needs the former. > **A `LIMIT` throws away tied winners** > > Using `ROW_NUMBER()` or a per-month `LIMIT 3` instead of `DENSE_RANK()` awards distinct numbers to equal totals and discards every product tied at the third spot, so the published board is wrong and non-deterministic about which tie it keeps. --- ## Common follow-up questions - Why can't you write `WHERE rnk <= 3` in the same SELECT that computes `rnk`, and where does it have to go instead? _(Tests whether the candidate keeps the standing filter outside the windowed SELECT and understands why a HAVING or WHERE on rnk in the same query fails.)_ - What changes in the output if you switch `DENSE_RANK` to `ROW_NUMBER`, and which is correct for a top-three that must keep tied products? _(Tests understanding of tie-handling semantics: `DENSE_RANK` preserves ties while `ROW_NUMBER` breaks them arbitrarily.)_ - The `in_stock` flag reflects a product's status today, not at sale time. How would the leaderboard change if a best-seller later went out of stock, and is filtering on it the right call? _(Tests whether the candidate can reason about the effect of the stock filter on historical results.)_ - If some `transactions` rows referenced a `product_id` with no matching row in `products`, how would the inner join change your results, and what would you do about it? _(Tests understanding of referential integrity assumptions baked into the join logic.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/kings_of_the_calendar) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.