Debug SQL join that drops rows
Company: Home Depot
Role: Product Analyst
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Onsite
You’re analyzing Home Improvement retail transactions to understand sales of **Mulch** during promotions. After joining multiple tables, your final result has **far fewer rows** than expected.
## Tables
Assume the following schemas (all dates are in the same timezone; `DATE` has no time component):
### `sales`
- `sale_id` (BIGINT, PK)
- `sale_date` (DATE)
- `store_id` (INT)
- `customer_id` (BIGINT)
### `sale_items`
- `sale_id` (BIGINT, FK → `sales.sale_id`)
- `line_id` (INT) — unique within a sale
- `product_id` (BIGINT)
- `qty` (INT)
- `unit_price` (DECIMAL(10,2))
### `products`
- `product_id` (BIGINT, PK)
- `category` (VARCHAR)
- `sub_category` (VARCHAR)
### `promotions`
- `promo_id` (BIGINT, PK)
- `product_id` (BIGINT)
- `start_date` (DATE)
- `end_date` (DATE)
- `discount_pct` (DECIMAL(5,2))
## Intended output
One row per day for the last 30 days:
- `sale_date`
- `mulch_units` = total units of mulch sold (promo + non-promo)
- `mulch_revenue` = total revenue from mulch
- `promo_mulch_units` = mulch units sold while an applicable promo was active
## Given (buggy) query
```sql
SELECT
s.sale_date,
SUM(i.qty) AS mulch_units,
SUM(i.qty * i.unit_price) AS mulch_revenue,
SUM(CASE WHEN p.promo_id IS NOT NULL THEN i.qty ELSE 0 END) AS promo_mulch_units
FROM sales s
JOIN sale_items i
ON s.sale_id = i.sale_id
JOIN products pr
ON pr.product_id = i.product_id
LEFT JOIN promotions p
ON p.product_id = i.product_id
AND s.sale_date BETWEEN p.start_date AND p.end_date
WHERE pr.category = 'Mulch'
AND p.discount_pct > 0
AND s.sale_date >= CURRENT_DATE - INTERVAL '30' DAY
GROUP BY 1;
```
## Task
1. Explain **why** this query can return **too few rows / too few units** compared to expectations.
2. Describe a **step-by-step debugging approach** to isolate which join/filter is dropping rows (e.g., incremental counts after each join).
3. Provide a corrected SQL query that produces the **intended output** (including non-promo mulch sales).
Overview: This question evaluates understanding of SQL joins, filtering interactions, aggregation, and troubleshooting data discrepancies when combining sales, sale_items, products, and promotions tables.
Read the full Home Depot Product Analyst interview experience this question came from
You are a Product Analyst at a home-improvement retailer. You need a **daily mulch sales report** for the trailing 30-day window.
There are four tables:
- **`sales`** — one row per transaction: `sale_id`, `sale_date`, `store_id`, `customer_id`.
- **`sale_items`** — one row per line item on a sale: `sale_id`, `line_id`, `product_id`, `qty`, `unit_price`.
- **`products`** — `product_id`, `category`, `sub_category`. "Mulch" products have `category = 'Mulch'`.
- **`promotions`** — `promo_id`, `product_id`, `start_date`, `end_date`, `discount_pct`. A line item is considered a **promo** sale when the product has at least one promotion whose `discount_pct > 0` and whose `[start_date, end_date]` window (inclusive) contains the `sale_date`.
**Task.** Produce exactly **one row for every calendar day in the last 30 days**, where the window is the 30 consecutive days ending on the most recent date that appears anywhere in `sales.sale_date` or `promotions.end_date` (use that as "today"; with the sample data the window is 2024-06-01 through 2024-06-30 inclusive). For each day, report mulch (`category = 'Mulch'`) totals:
- `sale_date` — the calendar day.
- `mulch_units` — total `qty` of mulch line items sold that day.
- `mulch_revenue` — total `qty * unit_price` of mulch line items sold that day, rounded to 2 decimals.
- `promo_mulch_units` — total `qty` of mulch line items sold that day that were on an active promotion (as defined above).
Every one of the 30 days must appear, even days with no mulch sales — those rows should show `0` for the three metrics (this is the part that a naive join drops). Order the result by `sale_date` ascending.
Note: a product can have several overlapping promotions on the same day, so the promo flag must be computed without multiplying the line-item rows.
Tables
sales(sale_id BIGINT, sale_date DATE, store_id INT, customer_id BIGINT)
sale_items(sale_id BIGINT, line_id INT, product_id BIGINT, qty INT, unit_price DECIMAL(10,2))
products(product_id BIGINT, category VARCHAR, sub_category VARCHAR)
promotions(promo_id BIGINT, product_id BIGINT, start_date DATE, end_date DATE, discount_pct DECIMAL(5,2))
Hints
- Build a 30-row calendar first (a 'date spine') and LEFT JOIN your daily mulch aggregates onto it, so days with no sales still appear with zeros.
- Compute the promo flag with EXISTS (or a semi-join) rather than joining to `promotions` directly — overlapping promotions on the same day would otherwise duplicate the line item and inflate the unit counts.