# Category-Specific Product Volume

> Sum transactions for a specific payment type.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The merchandising team is building a per-product Electronics exposure report. For each product, show how much transaction volume came specifically from the 'Electronics' category. Products that have never been part of an Electronics transaction should still appear with a zero.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests LEFT JOIN with a filtered condition and COALESCE for zero-filling. The requirement that products with no Electronics transactions show zero probes whether you know how to preserve all rows from one side of a join.

---

### Break down the requirements

#### Step 1: Start from all products

Use `products` as the left table to ensure every product appears in the output, regardless of transaction history.

#### Step 2: Left join with category filter

LEFT JOIN `transactions` on `product_id` with an additional join condition `AND p.category = 'Electronics'`. This ensures non-Electronics products get NULLs rather than being excluded.

#### Step 3: Aggregate with COALESCE

`COALESCE(SUM(t.total_amount), 0)` converts NULL sums (for products with no matching transactions) to zero.

---

### The solution

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

```sql
SELECT
    p.product_id,
    p.product_name,
    COALESCE(SUM(t.total_amount), 0) AS electronics_volume
FROM products p
LEFT JOIN transactions t
    ON p.product_id = t.product_id
    AND p.category = 'Electronics'
GROUP BY p.product_id, p.product_name
```

> **Cost Analysis**
>
> Left join of 20K products to 40M transactions. The category filter in the ON clause reduces the effective join to only Electronics products' transactions, significantly cutting I/O.

> **Interviewers Watch For**
>
> Placing the category filter in WHERE instead of ON converts the LEFT JOIN to an INNER JOIN, dropping non-Electronics products. This is the most common mistake and the core test of the question.

> **Common Pitfall**
>
> Using `WHERE p.category = 'Electronics'` would exclude all non-Electronics products from the result, violating the requirement to show every product with zero when applicable.

---

## Common follow-up questions

- What if you moved the category filter to the WHERE clause? _(Tests understanding of WHERE vs ON in LEFT JOIN semantics.)_
- How would you extend this to show volume for each category as separate columns? _(Tests conditional aggregation or PIVOT patterns.)_
- What if product_id had duplicates in the products table? _(Tests awareness of how duplicates in the left table would inflate sums.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/category_specific_product_volume)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.