Broken DAG: daily GMV doubled after line-item join
SQL data engineering interview problem. Difficulty: beginner. Pattern: Joins. About 15 minutes. Free to practice.
Every morning Finance looks at a dashboard that shows daily GMV (gross merchandise value, the total money from orders). Today the number is about twice as large as the orders table says it should be. Nothing crashed. The SQL simply counts some money more than once.
The editor contains the query that builds the daily_gmv table. It joins orders to order_items and then adds up order_total. Because an order can have several items, the join repeats the order row once for each item, and the same order_total is added several times. Fix the query so that it returns 1 row for each calendar day, with the columns order_date (taken from orders.created_at) and gmv (the sum of order_total, rounded to 2 decimal places). Leave out cancelled orders. Sort by order_date. Do not change the business rule about cancelled orders. Fix the problem with the repeated rows before you add anything up.
Requirements
- Every order must add its order_total to the day's total exactly once.
- Orders with order_status = 'cancelled' must not be counted.
- Return 1 row for each day, using the date part of created_at.
- Round gmv to 2 decimal places and sort by order_date.
Constraints
- The tables are orders (1 row per order) and order_items (1 row per product in an order).
- order_total lives on the orders table. It is the total of the whole order, not of one item.
- Return only these 2 columns, in this order: order_date, gmv.
Examples
Input: order_id | order_status | order_total | line_items ORD-0032 | paid | 725.97 | 2 ORD-0032 | paid | 725.97 | 2 Output: order_date | gmv 2026-08-01 | 3943.48 Why this passes: Joining to order_items repeats the header total once per line. Summing order_total after that join counts 725.97 twice for ORD-0032. GMV must sum each order once.
Topics: debugging, joins, aggregation.
More SQL interview questions · All interview problems · Learn data engineering
Broken DAG: daily GMV doubled after line-item join
Production ticket: Finance GMV spiked after someone joined orders to order_items before SUM(order_total).
The gold `daily_gmv` job joined `orders` to `order_items` and then summed `order_total`. Finance says GMV is roughly 2x what the orders fact should show. Fix the shipped SQL so: - order_date, gmv (ROUND to 2 decimals) - 1 row per calendar day from `orders.created_at` - exclude cancelled orders - ordered by order_date Do not change the business filter. Fix the grain before you aggregate.