Present and Accounted For

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

DoorDash

Problem

The merchandising team wants a per-product read on how much sales volume comes specifically from the 'Electronics' category. For every product, show the total transaction amount tied to Electronics, biggest first, and keep products that have never sold under Electronics in the list with a zero.

Every product lands on the report, even the ones Electronics never touched.

Tables

products

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

transactions

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

Expected output

Worked solution and explanation

What this problem really is

This is a conditional sum wearing a join costume. The real question: can you total each product's Electronics volume while still listing the products that sold zero Electronics? Anyone can sum the amounts. The trap is where the category filter lives. Push 'category = Electronics' into a WHERE clause and your LEFT JOIN silently collapses to an INNER JOIN, and every product that never sold an Electronics item vanishes from the report the business explicitly asked to see in full.

Break down the requirements

Step 1: Start from all products

LEFT JOIN products to transactions on product_id, driving from products so every product survives, even the ones with no transactions at all.

Step 2: Filter inside the aggregate, not in WHERE

Keep the category test inside the aggregate: SUM(CASE WHEN p.category = 'Electronics' THEN t.total_amount END). Non-Electronics rows contribute nothing to the sum, but the product row itself is never dropped.

Step 3: Zero-fill the empty case

A product with no Electronics volume produces a NULL sum. Wrap it in COALESCE(..., 0) so the report reads 0 for those products instead of a blank.

The solution

SELECT p.product_name,
       COALESCE(SUM(CASE WHEN p.category = 'Electronics' THEN t.total_amount END), 0) AS electronics_total
FROM products p
LEFT JOIN transactions t ON p.product_id = t.product_id
GROUP BY p.product_id, p.product_name
ORDER BY electronics_total DESC, p.product_name

Filter in the aggregate (correct)

SUM(CASE WHEN p.category = 'Electronics' THEN t.total_amount END) leaves every product row intact and simply contributes 0 for the non-Electronics ones. All products stay in the output.

Filter in WHERE (drops products)

WHERE p.category = 'Electronics' evaluates after the LEFT JOIN and discards every row where the match is absent, turning the outer join into an inner one. Products with no Electronics volume disappear entirely.

Common follow-up questions

• Walk me through what changes if you move the category predicate into the WHERE clause. (Tests understanding of WHERE vs ON vs conditional-aggregate semantics in a LEFT JOIN.)
• How would you extend this to show each category's volume as its own column in one row per product? (Tests conditional aggregation / pivot patterns.)
• What breaks if `product_id` is not unique in the products table? (Tests awareness of how duplicate keys in the left table inflate sums.)