Layout

Customer net collections without fan-out

PROBLEM 23 / 24 · ADVANCEDAll problems ↗

For every customer, return customer_id and net_collected_cents = captured payments minus succeeded refunds against those payments. Buyers without captured payments must have zero. Avoid duplicating payments when an order has multiple refund rows. Row order does not matter.

Outputcustomer_idnet_collected_cents

query.sql

DuckDB SQL

Restoring your saved draft…

⌘ / Ctrl + Enter to run

Run a query to see results.

Reveal solution & explanation
WITH r AS (SELECT payment_id, SUM(amount_cents) AS refund_cents FROM refunds WHERE status = 'succeeded' GROUP BY payment_id), p AS (SELECT o.customer_id, SUM(p.amount_cents - COALESCE(r.refund_cents, 0)) AS net_cents FROM payments p JOIN orders o ON o.order_id = p.order_id LEFT JOIN r ON r.payment_id = p.payment_id WHERE p.status = 'captured' GROUP BY o.customer_id) SELECT c.customer_id, COALESCE(p.net_cents, 0) AS net_collected_cents FROM customers c LEFT JOIN p ON p.customer_id = c.customer_id;

Reduce each one-to-many relationship before combining totals. Keeping the final join at customer grain also preserves zero-spend buyers.

Data & schema

customer_id
INTEGER · PK
customer_name
VARCHAR
country
VARCHAR
email
VARCHAR
signup_date
DATE
Table notes & relationships

Buyer accounts. Some buyers have no orders, and some email addresses are NULL.

Fictional teaching data. Money is USD cents; timestamps are UTC.