Captured cash minus successful refunds
Return a single row with captured_cents, refunded_cents, and net_collected_cents across the full dataset. Include captured payments and succeeded refunds only. Multiple refunds may reference one payment; avoid multiplying the captured amount by refund rows.
Output
captured_centsrefunded_centsnet_collected_centsquery.sql
DuckDB SQLRestoring your saved draft…
Run a query to see results.
Reveal solution & explanation
WITH p AS (SELECT COALESCE(SUM(amount_cents), 0) AS captured_cents FROM payments WHERE status = 'captured'), r AS (SELECT COALESCE(SUM(amount_cents), 0) AS refunded_cents FROM refunds WHERE status = 'succeeded') SELECT captured_cents, refunded_cents, captured_cents - refunded_cents AS net_collected_cents FROM p CROSS JOIN r;
Joining payment rows directly to multiple refund rows repeats the captured amount. Preaggregation protects the financial grain.
Data & schema
- payment_id
- INTEGER · PK
- order_id
- INTEGER
- status
- VARCHAR
- amount_cents
- INTEGER
- paid_on
- DATE
Table notes & relationships
Payment attempts. A failed attempt can precede a captured payment on the same order. Count captured money only.
order_id→ orders.order_idstatuscaptured / failed
Fictional teaching data. Money is USD cents; timestamps are UTC.