Kings of the Calendar

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

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.

Every month has a winner.

Tables

transactions

Columns: transaction_id INTEGER, user_id INTEGER, product_id INTEGER, quantity INTEGER, total_amount REAL, transaction_date TEXT

products

Columns: product_id INTEGER, product_name TEXT, category TEXT, price REAL, rating REAL, in_stock INTEGER

Expected output

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.

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

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

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.)