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.