Counted Twice

An easy AI Coding interview practice problem on DataDriven.

Domain
AI Coding
Difficulty
easy
Seniority
L4
Asked in
DoorDash

Problem

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).

Twice the revenue, half the truth.

Worked solution and explanation

The trap: a join that quietly moved the grain

This is a grain violation wearing a revenue-reporting costume. Each row of orders is one order, but join dropoffs fans it out to one row per drop, so a multi-stop order like o1 (two rows in dropoffs) shows up twice before you ever reach sum(amount_cents). Now the aggregate is summing over the wrong grain. Anyone can read the query; the tell is noticing that d.order_id is not unique in dropoffs, so the join is one-to-many and every downstream count inherits the fanout. Get it wrong and every restaurant with a multi-drop order over-reports revenue, the ~12% the partners are complaining about.

One `orders` row, many `dropoffs` rows: the join is one-to-many on `order_id`, so joining first and summing second double-counts `amount_cents`.
  • orders: order_id text PK, restaurant_id text, amount_cents integer, order_date text
  • dropoffs: dropoff_id text PK, order_id text FK, zone_id text
  • dropoffs.order_id references orders.order_id
The inflated version
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   /* one-to-many: o1 becomes 2 rows */
)
select restaurant_id, order_date,
       sum(amount_cents) as revenue_cents,       /* sums o1's 5000 twice */
       count(*) as order_count
from joined
group by restaurant_id, order_date

o1 fans out to two rows, so its 5000 is summed twice and count(*) counts drops, not orders.

Dedupe after the join
Reach for select distinct or distinct (order_id, amount_cents) to squash the extra rows. It passes the visible fixture, but it is patching the symptom on the wrong grain, and it is fragile the moment two real orders collide on a value.
Aggregate before the join
Roll dropoffs up to one row per order_id in a CTE, then join. The right side is now unique, the join is one-to-one, and sum(amount_cents) and count(*) are correct by construction, no de-duplication needed.
queries/`daily_restaurant_revenue`.sql
/* queries/daily_restaurant_revenue.sql */
/* Daily revenue per restaurant. Dropoffs are rolled up to one row per
   order BEFORE the join so a multi-stop order can't fan out revenue.
   zone_id is preserved as one representative zone per order. */

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

order_zone is unique on order_id, so the left join is one-to-one: revenue and order_count are per order, and the zone attribution survives.

Step 1: Collapse dropoffs to the order grain

Build order_zone as select order_id, min(zone_id) ... group by order_id. This guarantees exactly one row per order. min(zone_id) picks a stable representative when a multi-stop order spans zones, pick whatever attribution rule the partner-ping job actually wants, but keep it one row per order.

Step 2: Use a left join, not join

Drive from orders and left join the rolled-up zones. An inner join would silently drop any order with no dropoffs row, another way to lose revenue. Because the right side is now unique on order_id, the join cannot re-introduce fanout.

Common follow-up questions