Coding questions · Home Depot
Debug SQL join that drops rows
This question evaluates understanding of SQL joins, filtering interactions, aggregation, and troubleshooting data discrepancies when combining sales, sale_items, products, and promotions tables.
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 saleproduct_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_datemulch_units= total units of mulch sold (promo + non-promo)mulch_revenue= total revenue from mulchpromo_mulch_units= mulch units sold while an applicable promo was active
Given (buggy) query
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
- Explain why this query can return too few rows / too few units compared to expectations.
- Describe a step-by-step debugging approach to isolate which join/filter is dropping rows (e.g., incremental counts after each join).
- Provide a corrected SQL query that produces the intended output (including non-promo mulch sales).
Debug SQL join that drops rows
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 havecategory = '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 whosediscount_pct > 0and whose[start_date, end_date]window (inclusive) contains thesale_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— totalqtyof mulch line items sold that day.mulch_revenue— totalqty * unit_priceof mulch line items sold that day, rounded to 2 decimals.promo_mulch_units— totalqtyof 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.
Table sales
| column | type | constraints |
|---|---|---|
| sale_id | BIGINT | PRIMARY KEY |
| sale_date | DATE | |
| store_id | INT | |
| customer_id | BIGINT |
Sample data:
| customer_id | sale_date | sale_id | store_id |
|---|---|---|---|
| 10000 | 2024-06-03 | 1 | 10 |
| 10001 | 2024-06-06 | 2 | 10 |
| 10002 | 2024-06-06 | 3 | 11 |
| 10003 | 2024-06-20 | 4 | 10 |
| 10004 | 2024-06-21 | 5 | 10 |
| 10005 | 2024-06-28 | 6 | 12 |
| 10006 | 2024-06-15 | 7 | 10 |
| 10007 | 2024-05-31 | 8 | 10 |
| 10008 | 2024-06-10 | 9 | 14 |
Table sale_items
| column | type | constraints |
|---|---|---|
| sale_id | BIGINT | |
| line_id | INT | |
| product_id | BIGINT | |
| qty | INT | |
| unit_price | DECIMAL(10,2) |
Sample data:
| line_id | product_id | qty | sale_id | unit_price |
|---|---|---|---|---|
| 1 | 1001 | 3 | 1 | 4.99 |
| 2 | 2001 | 1 | 1 | 19.99 |
| 1 | 1001 | 2 | 2 | 4.79 |
| 2 | 1002 | 1 | 2 | 5.49 |
| 1 | 1001 | 1 | 3 | 4.79 |
| 2 | 2001 | 2 | 3 | 18.99 |
| 1 | 1002 | 4 | 4 | 4.99 |
| 2 | 1001 | 1 | 4 | 4.99 |
| 1 | 1002 | 2 | 5 | 4.99 |
| 2 | 1001 | 2 | 5 | 4.99 |
| 1 | 1001 | 5 | 6 | 4.59 |
| 2 | 2001 | 1 | 6 | 17.99 |
| 1 | 1001 | 2 | 7 | 4.99 |
| 1 | 1002 | 1 | 8 | 5.49 |
| 2 | 1001 | 1 | 8 | 4.99 |
| 1 | 1001 | 1 | 9 | 4.79 |
Table products
| column | type | constraints |
|---|---|---|
| product_id | BIGINT | PRIMARY KEY |
| category | VARCHAR | |
| sub_category | VARCHAR |
Sample data:
| category | product_id | sub_category |
|---|---|---|
| Mulch | 1001 | Brown |
| Mulch | 1002 | Black |
| Garden | 2001 | Hose |
Table promotions
| column | type | constraints |
|---|---|---|
| promo_id | BIGINT | PRIMARY KEY |
| product_id | BIGINT | |
| start_date | DATE | |
| end_date | DATE | |
| discount_pct | DECIMAL(5,2) |
Sample data:
| discount_pct | end_date | product_id | promo_id | start_date |
|---|---|---|---|---|
| 10 | 2024-06-10 | 1001 | 5001 | 2024-06-05 |
| 15 | 2024-06-25 | 1002 | 5002 | 2024-06-20 |
| 5 | 2024-06-30 | 1001 | 5003 | 2024-06-28 |
| 0 | 2024-06-16 | 1001 | 5004 | 2024-06-15 |
Expected output
30 rows, one per day from 2024-06-01 to 2024-06-30, with daily mulch units, revenue, and promo units; zero-sale days show zeros; ordered by sale_date.
| mulch_revenue | mulch_units | promo_mulch_units | sale_date |
|---|---|---|---|
| 0.00 | 0 | 0 | 2024-06-01 |
| 0.00 | 0 | 0 | 2024-06-02 |
| 14.97 | 3 | 0 | 2024-06-03 |
| 0.00 | 0 | 0 | 2024-06-04 |
| 0.00 | 0 | 0 | 2024-06-05 |
| 19.86 | 4 | 3 | 2024-06-06 |
| 0.00 | 0 | 0 | 2024-06-07 |
| 0.00 | 0 | 0 | 2024-06-08 |
| 0.00 | 0 | 0 | 2024-06-09 |
| 4.79 | 1 | 1 | 2024-06-10 |
| 0.00 | 0 | 0 | 2024-06-11 |
| 0.00 | 0 | 0 | 2024-06-12 |
| 0.00 | 0 | 0 | 2024-06-13 |
| 0.00 | 0 | 0 | 2024-06-14 |
| 9.98 | 2 | 0 | 2024-06-15 |
| 0.00 | 0 | 0 | 2024-06-16 |
| 0.00 | 0 | 0 | 2024-06-17 |
| 0.00 | 0 | 0 | 2024-06-18 |
| 0.00 | 0 | 0 | 2024-06-19 |
| 24.95 | 5 | 4 | 2024-06-20 |
| 19.96 | 4 | 2 | 2024-06-21 |
| 0.00 | 0 | 0 | 2024-06-22 |
| 0.00 | 0 | 0 | 2024-06-23 |
| 0.00 | 0 | 0 | 2024-06-24 |
| 0.00 | 0 | 0 | 2024-06-25 |
| 0.00 | 0 | 0 | 2024-06-26 |
| 0.00 | 0 | 0 | 2024-06-27 |
| 22.95 | 5 | 5 | 2024-06-28 |
| 0.00 | 0 | 0 | 2024-06-29 |
| 0.00 | 0 | 0 | 2024-06-30 |
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
promotionsdirectly — overlapping promotions on the same day would otherwise duplicate the line item and inflate the unit counts. - A predicate like
WHERE p.discount_pct > 0placed after a LEFT JOIN quietly converts it into an INNER JOIN and removes the non-promo rows; keep promo logic out of the outer WHERE.