Fix the customer revenue rollup for a churn report
Your task
A report query joined customers to orders to refunds and summed in one pass, so an order with two refunds was counted twice. An AI also described it as leaving out customers with no orders, which it did not. You rebuild the numbers for the churn dashboard one grain at a time: customers, then orders, then refunds.
An assistant wrote summarize_customer_orders(customer_ids, order_lines, refund_lines). It is in the editor. It has a bug. Find it and fix it.
Input and output:
customer_ids: the customers. Ids may repeat.order_lines: each"order_id|customer_id|amount_cents".refund_lines: each"refund_id|order_id|amount_cents".- Return one
"customer_id|order_count|net_revenue_cents"per distinct customer, sorted by customer id in ascending order by plain string comparison.
Rules the tests check:
- Every distinct customer appears, including customers with no orders, who get
|0|0. - An amount is an optional
-followed by one or more ASCII digits. Any other amount text counts as 0. - Orders, in order: skip a line that does not split on
|into exactly 3 fields, or whose customer is not incustomer_ids. Skip an order id that was already counted: a replayed row is the same order. Otherwise add 1 to the customer's count and its amount to their gross. - Refunds, in order: skip a line that does not split into exactly 3 fields. Skip a refund id that was already counted. Skip a refund whose order was not counted, and do not mark that refund id as counted. Otherwise subtract its amount from the order's customer. Different refund ids on one order all count.
- Net revenue is gross minus counted refunds.
Examples
summarize_customer_orders(customer_ids: list[str], order_lines: list[str], refund_lines: list[str]) -> list[str]Example 1
Input
summarize_customer_orders(["c1", "c2", "c3"], ["o1|c1|1000", "o2|c1|2500", "o3|c2|4000"], ["r1|o1|250", "r2|o1|250"])Output
["c1|2|3000", "c2|1|4000", "c3|0|0"]Example 2
Input
summarize_customer_orders(["c9"], [], [])Output
["c9|0|0"]