The Ones Who Carry Us

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

Problem

Which products have brought in at least one hundred dollars across every sale in the transactions log, and how much revenue and how many units has each sold? The general manager reviewing product performance wants them listed by catalog product name, biggest earner first.

Every quarter, a few names hold up the whole line. Name them.

Tables

products

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

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

What this problem really is

Strip away the performance-review framing and this is a labeled aggregation with a threshold. You sum revenue per product, attach the product's name so the output is readable, and keep only the products whose total clears one hundred dollars. Anyone can write the join. The tell is where the threshold goes: a filter on the summed revenue has to run after the rows are grouped, which means HAVING, not WHERE. Put the sum in WHERE and the query errors. Filter individual sales of one hundred dollars or more instead of the product's total and you quietly report the wrong leaderboard.

Break down the requirements

Step 1: Join products to transactions

The join lines up each sale with its catalog row on the shared product_id. That is what lets the summed revenue sit next to a human-readable product_name in the output. Sales with no product recorded have nothing to name, so the inner join drops them.

Step 2: Aggregate by p.product_name

GROUP BY p.product_name collapses every sale of a product into one row, and SUM(t.total_amount) plus SUM(t.quantity) compute the revenue and units for that product. Refunds and voided sales are summed as recorded.

Step 3: Filter groups with HAVING

The one hundred dollar cut is a condition on the summed revenue, so it belongs in HAVING. WHERE cannot see the sum because it is evaluated before grouping happens.

Step 4: Sort the final output

ORDER BY total_revenue DESC puts the biggest earners on top, matching how the general manager reads the review from best to worst.

The solution

SELECT p.product_name, SUM(t.total_amount) AS total_revenue, SUM(t.quantity) AS units_sold
FROM products p
JOIN transactions t ON p.product_id = t.product_id
GROUP BY p.product_name
HAVING SUM(t.total_amount) >= 100
ORDER BY total_revenue DESC

Common follow-up questions

• Some `transactions.product_id` values are NULL, and the inner join drops those sales entirely. Is excluding that revenue correct, or should the business see an 'unattributed' bucket? (Tests whether the candidate notices that an inner join silently discards rows with a null join key.)
• With millions of transactions, how would an index on `transactions.product_id` change the join and aggregation plan compared with a full scan? (Probes how indexing the join and grouping columns changes the execution plan at scale.)
• If new transactions keep arriving, how would you maintain per-product revenue totals incrementally instead of re-aggregating the whole table each run? (Tests understanding of incremental aggregation patterns for late-arriving data.)