SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 237 of 250

Hard

Build a balance from charges and refunds

Your task

Return order_id, charge_cents, refund_cents, and net_cents for every ledger order. Show positive charge and refund totals, using zero when a kind is absent. Net is charges minus refunds. Sort by order ID.

A single order can have split charges or partial refunds. Count each ledger entry once in the balance.

Sample data

Table:payment_ledger
entry_idorder_idkindamount_cents
1201charge2500
2202charge1800
3203charge1100
4203charge2100
5204charge5000
6204refund5000
7205charge2500
8206charge4000
9208charge1700
10209charge2900

Preview of 10 of 13 rows

Expected output

order_idcharge_centsrefund_centsnet_cents
201250002500
202180001800
203320003200
204500050000
205250002500
20640005003500
2079000900
208170001700
209290002900
9998000800
Show a hint

Use separate conditional sums for charge and refund entries, then subtract the two totals.

Data quality and reconciliation guide

Keep raw values available while you clean them

The staged_orders table is an import, so references, statuses, and amounts are text and may contain missing or malformed values. Trim whitespace before testing a field. An empty string after trimming can be turned into NULL to give missing data one representation.

SELECT import_id, NULLIF(TRIM(order_ref), '') AS clean_ref
FROM staged_orders
ORDER BY import_id;

Compare records at the same grain

The payment ledger may have several charges or refunds for one order. Aggregate that ledger to one row per order before comparing it with shop_orders. Keep unmatched keys visible so a reconciliation reveals missing orders and missing payments.

SELECT order_id,
  SUM(CASE WHEN kind = 'charge' THEN amount_cents ELSE -amount_cents END) AS net_cents
FROM payment_ledger
GROUP BY order_id
ORDER BY order_id;
SQLiteCtrl / ⌘ + Enter

Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.

Results

Loading SQLite…