Top Products by Quantity Sold

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

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

ESPN

Problem

An analyst closing the 2026 books needs the units sold per product from the transactions table, with sales logged without a product kept together on a single line. List each product with its total quantity, highest volume first.

The bestsellers. By volume.

Tables

transactions

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

Expected output

Worked solution and explanation

A sum dressed up as a leaderboard

This is a sum over a measure dressed up as a sales leaderboard. Filtering to 2026 and sorting is the easy half. What separates candidates is reading 'total quantity sold' as units, SUM(quantity), instead of counting the rows of transactions. Each sale carries 0 to 5 units, so COUNT(*) quietly turns a volume board into a frequency board: a voided 0-unit sale scores 1, a 5-unit sale scores 1, and the order scrambles. The second tell is the 30 units sold without a product_id. They belong on the board as one line, and they top it.

Step 1: Scope the sales to 2026

transaction_date is text in 'YYYY-MM-DD' form, and the table holds sales from 2022 on. Pull out the year and keep only '2026'. Leave the filter off and earlier years pile onto the same products, so every total inflates.

Step 2: Add up quantity per product_id

One output row per product, and the number on it is units: SUM(quantity) AS total_quantity. A product with two 3-unit sales has moved 6 units, while COUNT(*) would call it 2. Name the column for what it is; that name is half the answer's contract.

Step 3: Keep the sales with no product

GROUP BY product_id collects every NULL key into a single group, which is exactly the line the ask wants. Do not add WHERE product_id IS NOT NULL to tidy up, because that group is the largest on the board.

Step 4: Sort by volume, highest first

ORDER BY total_quantity DESC puts the biggest sellers on top. Plenty of products land on the same total, but the ask fixes only the volume order, so tied products may come back in any order and no tiebreaker is owed.

SELECT
  product_id,
  SUM(quantity) AS total_quantity
FROM transactions
WHERE strftime('%Y', transaction_date) = '2026'
GROUP BY product_id
ORDER BY total_quantity DESC

Counting sales

COUNT(*) per product_id measures how often a product sold. The voided sale 1335 of product 1197 records quantity 0 and still scores 1.

Adding up units

SUM(quantity) measures how much moved. That voided sale adds 0, and a 5-unit sale counts five times as much as a 1-unit one.

Common follow-up questions

• Rewrite the year filter so the engine can skip partitions on `transaction_date`. (Whether you know a function around the partition key can defeat pruning.)
• Now return only the top 10 products. What happens to products tied at the cutoff? (A `LIMIT` through a tie is the one place a tie rule becomes part of the answer.)
• How does the query change if refunds should net out of the unit totals? (Reaching for a conditional sum keyed off `total_amount` instead of trusting raw `quantity`.)
• What does the plan look like when `product_id` runs into the millions? (Hash aggregation memory, spill to disk, and partial aggregation before the shuffle.)