SQL Debug: Counted Twice

Restaurant partners are seeing ~12% inflated revenue in our partner-ping job. The job builds daily_restaurant_revenue from the orders + dropoffs tables. Run tests/test_no_double_count.py to reproduce: rows where one order has multiple dropoffs (multi-stop deliveries) get counted once per dropoff. Fix the SQL in queries/daily_restaurant_revenue.sql so revenue is per order, not per (order, dropoff).

queries/daily_restaurant_revenue.sql

/* queries/daily_restaurant_revenue.sql */
/* Daily revenue per restaurant. Joined to dropoffs so we can attribute
   to delivery zone for the partner ping job. */

with joined as (
    select
        o.restaurant_id,
        o.order_id,
        o.amount_cents,
        o.order_date,
        d.zone_id
    from orders o
    join dropoffs d on d.order_id = o.order_id
)
select
    restaurant_id,
    order_date,
    sum(amount_cents) as revenue_cents,
    count(*) as order_count
from joined
group by restaurant_id, order_date

/* queries/daily_restaurant_revenue.sql */
/* Daily revenue per restaurant. Joined to dropoffs so we can attribute
   to delivery zone for the partner ping job. */

with joined as (
    select
        o.restaurant_id,
        o.order_id,
        o.amount_cents,
        o.order_date,
        d.zone_id
    from orders o
    join dropoffs d on d.order_id = o.order_id
)
select
    restaurant_id,
    order_date,
    sum(amount_cents) as revenue_cents,
    count(*) as order_count
from joined
group by restaurant_id, order_date

Active Now|Data Engineer (L4)|||3.6k Attempts|1.0k Solves|