# Present and Accounted For

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

Canonical URL: <https://datadriven.io/problems/present_and_accounted_for>

Domain: SQL · Difficulty: easy · Seniority: L3

## 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.

## 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

**Left join with a filtered condition and zero-fill**

```sql
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.

> **Cost analysis**
>
> Left join of 20K products to 40M transactions. Grouping by product_id keeps the aggregation keyed on the small side, and the conditional only accumulates the Electronics slice, so the heavy scan does one pass with no post-join filter to re-materialize dropped rows.

> **Interviewers watch for**
>
> The moment a candidate reaches for WHERE p.category = 'Electronics', they have quietly answered a different question. The tell of a strong candidate is keeping the predicate inside the SUM(CASE...) precisely so the zero-volume products survive.

> **Common pitfall**
>
> Filtering category in WHERE excludes every non-Electronics product from the result, violating the requirement that every product appears with a zero when it has no Electronics volume. It is the single most common way to fail this question.

---

## 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.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/present_and_accounted_for)
- [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.