Fix a join that doubles revenue

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Joins. About 14 minutes. Part of the Pro drill bank.

The finance team says the daily revenue is about double what it should be. The query joins rev_orders to rev_payments. An order can have several payment rows (a split payment, a failed try, a refund), so joining repeats the order row once for each payment, and the order's amount is added several times.

rev_orders has 1 row per order, with the order total in amount. rev_payments has one or more rows per order, each with a status (captured, failed or refunded). For each order_date, return the sum of amount for the orders that have at least one payment with status = 'captured'. Each order must count once, no matter how many captured payments it has. Return order_date and revenue, sorted by order_date.

Requirements

• Count an order only if it has at least one captured payment.
• Each order adds its amount exactly once, even with several captured payments.
• Orders with only failed or refunded payments, or with no payments, are not counted.
• Return order_date and revenue, sorted by order_date.

Constraints

• The tables are rev_orders and rev_payments.
• amount is the order total and is stored once per order. paid_amount is the amount of one payment, and you should not use it here.
• status is one of captured, failed or refunded.

Examples

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