Debug row loss after SQL joins

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

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.