Debug row loss after SQL joins
Company: Homedepot
Role: Data Analyst
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
You are reviewing a buggy SQL query used by a retail analytics team at a home-improvement company. The query is supposed to return **daily promo sales for mulch products** during the **Spring Mulch Promo**.
### Tables
`orders`
- `order_id` BIGINT
- `customer_id` BIGINT
- `store_id` INT
- `order_ts_utc` TIMESTAMP
- `order_status` VARCHAR
`order_items`
- `order_id` BIGINT
- `line_id` INT
- `product_id` INT
- `quantity` INT
- `unit_price` NUMERIC(10,2)
`products`
- `product_id` INT
- `product_name` VARCHAR
- `category` VARCHAR
`promotions`
- `promo_id` INT
- `promo_name` VARCHAR
- `product_id` INT
- `store_id` INT NULL
- `start_date` DATE
- `end_date` DATE
- `is_national` BOOLEAN
`stores`
- `store_id` INT
- `region` VARCHAR
- `timezone` VARCHAR
### Business rules
- Analyze orders from **2025-03-01 to 2025-05-31**.
- Only include orders with `order_status = 'DELIVERED'`.
- Only include products where `category = 'Mulch'`.
- Reporting should use **America/New_York** as the business timezone.
- A promotion can be either:
- **store-specific** (`promotions.store_id` is populated), or
- **national** (`promotions.store_id` is `NULL` and applies to all stores).
### Expected output
Return one row per:
- `order_date_et` DATE
- `store_id` INT
- `product_id` INT
with:
- `units_sold`
- `gross_revenue`
### Buggy query
```sql
SELECT
DATE(o.order_ts_utc) AS order_date_et,
s.store_id,
p.product_id,
SUM(oi.quantity) AS units_sold,
SUM(oi.quantity * oi.unit_price) AS gross_revenue
FROM orders o
JOIN order_items oi
ON o.order_id = oi.order_id
JOIN products p
ON oi.product_id = p.product_id
JOIN promotions pr
ON p.product_id = pr.product_id
JOIN stores s
ON pr.store_id = s.store_id
WHERE o.order_status = 'DELIVERED'
AND p.category = 'Mulch'
AND pr.promo_name = 'Spring Mulch Promo'
AND DATE(o.order_ts_utc) BETWEEN pr.start_date AND pr.end_date
AND o.store_id = s.store_id
GROUP BY 1,2,3;
```
### Task
1. Explain why this query may return **far fewer rows than expected** after the joins.
2. Identify the logical bugs, especially around **join conditions** and **date handling**.
3. Rewrite the query so it correctly captures mulch orders that occurred during the promotion window, including both **store-specific** and **national** promotions.
Quick Answer: This question evaluates SQL data manipulation and JOIN logic skills, including temporal and timezone-aware date handling and aggregation, within the Data Manipulation (SQL/Python) domain and primarily tests practical application-level competency.