Layout

Captured cash minus successful refunds

PROBLEM 11 / 24 · BASICAll problems ↗

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.

Outputcaptured_centsrefunded_centsnet_collected_cents

query.sql

DuckDB SQL

Restoring your saved draft…

⌘ / Ctrl + Enter to run

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_id
  • status captured / failed

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