Debug row loss after SQL joins

Q: Debug row loss after SQL joins

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.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

Q: What difficulty level is this interview question?

This is a medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Onsite rounds at Homedepot.

Q: What role is this question designed for?

This question is commonly asked for Data Analyst candidates at Homedepot during technical interviews.

Question

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

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

Explain why this query may return far fewer rows than expected after the joins.
Identify the logical bugs, especially around join conditions and date handling .
Rewrite the query so it correctly captures mulch orders that occurred during the promotion window, including both store-specific and national promotions.