SQL data engineering interview problem. Difficulty: intermediate. Pattern: Joins. About 14 minutes. Part of the Pro drill bank.
rev_orders has one row per order. rev_payments has one or more rows per order (split payments, failures, refunds), each with a status. For each order_date, return the sum of amount over orders that have at least one payment with status = 'captured'. Every order counts once, no matter how many captured payments it has. Columns: order_date, revenue. Order by order_date.
Input: rev_orders order_id | order_date | amount 1 | 2024-03-01 | 100 2 | 2024-03-01 | 50 3 | 2024-03-02 | 80 4 | 2024-03-02 | 60 5 | 2024-03-03 | 40 rev_payments payment_id | order_id | method | status | paid_amount 1 | 1 | card | captured | 60 2 | 1 | wallet | captured | 40 3 | 2 | card | failed | 50 4 | 3 | card | captured | 50 5 | 3 | upi | captured | 30 6 | 3 | card | refunded | 10 7 | 5 | card | captured | 10 8 | 5 | card | captured | 10 9 | 5 | upi | captured | 20 Output: order_date | revenue 2024-03-01 | 100 2024-03-02 | 80 2024-03-03 | 40 Why this passes: Orders 1, 3 and 5 have a captured payment (order 2 only failed, order 4 has none). Each is counted once: 100 on Mar 1, 80 on Mar 2, 40 on Mar 3.
Input: rev_orders order_id | order_date | amount 1 | 2024-03-01 | 100 2 | 2024-03-02 | 70 rev_payments payment_id | order_id | method | status | paid_amount 1 | 1 | card | captured | 60 2 | 1 | wallet | captured | 40 3 | 2 | card | captured | 70 Output: order_date | revenue 2024-03-01 | 100 2024-03-02 | 70 Why this passes: Order 1 has two captured payments and still counts once.
Topics: lakebench, sql, joins, fan-out, revenue.
More SQL interview questions · All interview problems · Learn data engineering
Interview-style drill: Report daily revenue for paid orders when each order can have several payment rows.
`rev_orders` has one row per order. `rev_payments` has one or more rows per order (split payments, failures, refunds), each with a `status`. For each `order_date`, return the sum of `amount` over orders that have at least one payment with `status = 'captured'`. Every order counts once, no matter how many captured payments it has. Columns: `order_date`, `revenue`. Order by `order_date`.