# What the Shelf Never Sold > Some products never moved a single unit. They still belong on the report. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 ## Problem A merchandising team needs a revenue report spanning the whole catalog, where a product's revenue is the total amount buyers were charged across all of its sales. List every product with that revenue rounded to two decimals, and a product that never sold a single unit should read zero. ## Worked solution and explanation ### What this really is This is an outer join wearing a finance hat, and the whole problem is a question about which table defines the universe. Anyone can add up sales. The candidates who pass are the ones who notice the report's spine is the catalog, not the sales: the answer must contain a row for a product that no one ever bought. Anchor on transactions and those products vanish without a trace; lean on a plain SUM and the unsold ones come back NULL instead of 0. Both mistakes produce a query that runs clean and is quietly wrong. --- ### How to get there #### Step 1: Make the catalog the driver Start from products and attach transactions as the optional side with a LEFT JOIN. That single decision is what guarantees Premium Widget 10X, which has no matching sale, still shows up. An INNER JOIN here is the difference between a 10-row report and a 9-row one, and nothing in the result warns you which you got. #### Step 2: Sum the right number Revenue here is the money actually collected: sum the total_amount recorded on each of a product's sales. The catalog carries a price column and the sales carry a quantity, and multiplying them is the trap, it looks like revenue but it ignores every discount and does not match what was charged. Check the sales rows, a product priced at 17.52 selling 2 units shows 23.46 on the sale, not 35.04, so the money collected lives in total_amount, not in price times quantity. #### Step 3: Turn the empty sum into zero, in cents For an unsold product there are no rows to add, and SUM over nothing returns NULL, not zero. Wrap it in COALESCE(..., 0) so every product reports a real number, and round the whole thing to two decimals so the report reads in clean dollars and cents. Because total_amount is a REAL column, summing many of them can leave floating-point noise past the second decimal; rounding to cents pins the value so it matches cleanly. --- ### The solution **Revenue per product, unsold products included** ```sql 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 that have at least one matching sale. Premium Widget 10X disappears entirely and the report silently shrinks to 9 rows. No error, no warning, just a missing product. **LEFT JOIN (correct)** Keeps every product and lets the unmatched ones carry a NULL sum, which COALESCE then turns into 0. The report stays at 10 rows and the unsold product reads 0. > **Common pitfall** > > Two failures travel together on this shape: an INNER JOIN that erases the products with no sales, and a bare SUM that returns NULL for them when you do keep the row. The give-away in the prompt is words like 'every product' and 'never sold', they are signalling outer join plus a zero fallback. > **Interviewers watch for** > > The tell of a senior candidate is reaching for the LEFT JOIN before being prodded, and saying out loud why SUM of an empty group is NULL rather than 0. Reciting the COALESCE without explaining the NULL it defends against reads as memorized, not understood. > **At scale** > > At 20,000 products against 60M transactions, the engine aggregates the large side by product_id and joins into the small catalog, so the catalog acts as the driving dimension. An index on transactions.product_id keeps the join a series of seeks instead of a full 15GB scan; the GROUP BY collapses the fact rows long before the result is materialized. ## 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 sold and reuses the same outer join.)_ - If you used COUNT(*) instead of COUNT on a transactions column, what would an unsold product report and why? _(Probes understanding 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 without re-dropping the zero-revenue products.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/what_the_shelf_never_sold) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.