What the Shelf Never Sold

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

L3

Asked in

General Assembly

Problem

List every product in the catalog with its revenue, the sum of what buyers were charged across all of its transactions, rounded to two decimals, and have a product that never sold read zero. The merchandising team reads the report walking the catalog from the lowest product number to the highest.

Some products never moved a single unit. They still belong on the report.

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 really is

This is an outer join wearing a finance hat. The whole problem comes down to which table defines the universe. Anyone can add up sales. The candidates who pass notice that the report's spine is the catalog, not the sales. On this data only 91 product ids ever appear in transactions, against 200 rows in products, so the answer owes a row to more than a hundred products nobody bought. Anchor on transactions and those rows vanish without a trace. Keep them with an outer join but trust a bare SUM, and they come back NULL instead of 0. Both mistakes run clean and are quietly wrong.

How to get there

Step 1: Make products the driver

Start from products and attach transactions as the optional side with a LEFT JOIN on product_id. That one decision keeps a product with no sale in the report. Flip it to an INNER JOIN and the 200-row report shrinks to the products that sold, with nothing in the output warning you. Sales whose product_id is empty attach to no catalog row, so they fall away, which is what a per-product report wants.

Step 2: Sum the right number

Revenue here is the money actually collected, so sum the total_amount recorded on each sale. The catalog carries price and each sale carries quantity. Multiplying them is the trap: it looks like revenue but ignores what was actually charged. The first sample rows prove it: product 1001 is priced at 17.52, yet a 2-unit sale of it shows 23.46, not 35.04. The voided sale at 0.0 and the -45.0 refund belong in the sum too, because they are what was charged.

Step 3: Turn the empty sum into zero, in cents

For an unsold product, the join hands the group one row whose total_amount is NULL. SUM over nothing but NULL returns NULL, not zero. Wrap it in COALESCE(..., 0) so every product reports a number. Then apply ROUND(..., 2): total_amount is REAL, and adding floats leaves noise past the cents that the grade would see.

Step 4: Walk the catalog in product order

The report is read from the lowest product number up, so it ends with ORDER BY p.product_id. Product ids are unique in products, so that order is total and needs no tiebreaker.

The solution

SELECT p.product_id,
       p.product_name,
       ROUND(COALESCE(SUM(t.total_amount), 0), 2) AS total_revenue
FROM products p
LEFT JOIN transactions t ON p.product_id = t.product_id
GROUP BY p.product_id, p.product_name
ORDER BY p.product_id

INNER JOIN (wrong)

Keeps only products with at least one matching sale. Every product nobody bought disappears, and the report silently shrinks from 200 rows to the 91 that sold. No error, no warning, just missing products.

LEFT JOIN (correct)

Keeps all 200 products. The unmatched ones carry a NULL sum, which COALESCE turns into 0, so every row reads a real number.

Common follow-up questions

• Add a column for the number of units sold per product. How do you keep an unsold product reading 0 there too? (Tests whether the candidate separates revenue from units and reuses the same outer join with a default on `SUM(t.quantity)`.)
• If you used `COUNT(*)` instead of counting a `transactions` column, what would an unsold product report, and why? (Probes whether the candidate knows that `COUNT(*)` counts the preserved left row even with no match, while `COUNT(t.transaction_id)` does not.)
• Now return only products whose total revenue is below 100, but still include the ones that never sold. Where does that filter go? (Pushes toward filtering after the aggregate, on the defaulted value, without re-dropping the zero-revenue products.)