Customer net collections without fan-out
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.
Output
customer_idnet_collected_centsquery.sql
DuckDB SQLRestoring your saved draft…
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
- 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.