The Heaviest Carts

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

Domain

SQL

Difficulty

medium

Seniority

mid

Asked in

DoorDash

Problem

Which three products earned the most revenue in each age bucket, counting only purchases in transactions made by customers whose account in users is active right now? We read the cohorts youngest first, each one's products from the biggest earner down.

Inside every age group, a few customers carry the basket. Find them.

Tables

transactions

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

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

Expected output

Worked solution and explanation

Why this problem exists in real interviews

This is a per-group top-N over a filtered, pre-aggregated population, dressed up as merchandising. The skill being probed: can you roll total_amount up to one number per (age_bucket, product_id), scoped to active accounts, then rank inside each cohort without letting one busy cohort swamp the rest? Anyone can sum revenue by product; what separates people is scoping the leaderboard to one cohort at a time AND pushing the account_status = 'active' filter in before the aggregate. Aggregate the full customer base and you credit revenue to cohorts no active shopper spent in; reach for ROW_NUMBER and a tie at the cutoff silently drops a real top product.

Break down the requirements

Step 1: Filter to active accounts, attach the cohort, and roll revenue up per product

age_bucket lives on users, spend lives on transactions, so you JOIN on user_id, keep only rows where account_status = 'active', and GROUP BY age_bucket plus product_id with SUM(total_amount). One row per (cohort, product) is the grain everything downstream depends on. Drop the account_status filter and inactive and suspended accounts pad every cohort's revenue; group by product_id alone and you lose the cohort; forget to sum and you rank single transactions instead of the product's real pull.

Step 2: Rank inside each cohort, not across all cohorts

PARTITION BY age_bucket restarts the ranking for every cohort, ORDER BY total_revenue DESC puts the biggest earner at position 1. Forget the PARTITION BY and you get a single global leaderboard, so only the richest cohort ever shows up in the top 3.

Step 3: Keep rank <= 3 and shape the output

Filter the windowed result to revenue_rank <= 3, then ORDER BY age_bucket, total_revenue DESC so the cohorts read youngest first and each cohort's leaderboard reads top down. The window value has to be computed before you can filter on it, which is why it lives in a CTE. Return only age_bucket, product_id, and total_revenue; pulling extra columns into the SELECT changes the result shape the grader compares against.

The solution

WITH product_revenue AS (
  SELECT u.age_bucket,
         t.product_id,
         SUM(t.total_amount) AS total_revenue
  FROM transactions t
  JOIN users u ON u.user_id = t.user_id
  WHERE u.account_status = 'active'
  GROUP BY u.age_bucket, t.product_id
),
ranked AS (
  SELECT age_bucket,
         product_id,
         total_revenue,
         DENSE_RANK() OVER (PARTITION BY age_bucket ORDER BY total_revenue DESC) AS revenue_rank
  FROM product_revenue
)
SELECT age_bucket, product_id, total_revenue
FROM ranked
WHERE revenue_rank <= 3
ORDER BY age_bucket, total_revenue DESC;

`ROW_NUMBER`

Always 1,2,3,4... even on ties. Two products tied for 3rd: one is labelled 3, the other 4 and is cut. Which one survives depends on tiebreak chance.

`DENSE_RANK`

Ties share a rank: 1,2,3,3,4. Both products tied for 3rd are kept. The result set is stable no matter how the engine orders equal rows.

Common follow-up questions

• Show only the single top product per cohort, and if two products tie return both. (Tests whether they keep `DENSE_RANK` with rank = 1 rather than switching to `ROW_NUMBER`, which would arbitrarily drop a tied leader.)
• Now rank products by their total revenue across all cohorts combined, not within each cohort. (Forces reasoning about the (cohort, product) grain: the same `product_id` summed across cohorts, ranked globally, is a different aggregation.)
• Rank by revenue earned in the last 90 days instead of all time. (Probes pushing a `transaction_date` predicate into the CTE alongside the status filter and understanding it narrows the population before the aggregate.)