Drill 7 of 12 in Interview Classics
0

Interview Classics · SUM(CASE) · signed amounts · LEFT JOIN + COALESCE

7. Wallet balance from a ledger

Concept: SUM(CASE) · signed amounts · LEFT JOIN + COALESCEshow

Ledgers usually store every amount as a positive number plus a type, so a balance is a signed sum: add credits and subtract debits with SUM(CASE WHEN type = 'credit' THEN amount ELSE -amount END). Users with no entries need a LEFT JOIN, and COALESCE(..., 0) turns their NULL sum into 0.

Example (different data):

SELECT sku,
  SUM(CASE WHEN direction = 'in' THEN qty ELSE -qty END) AS on_hand
FROM stock_moves
GROUP BY sku;

Your task

Fintech classic: the wallet team needs every user's current balance, rebuilt from the raw ledger of credits and debits.

Return username and balance (credits minus debits) for every user, with 0 for users who have no ledger entries. Sort by balance (highest first), then username.

Starting Postgres in your browser…

Checking your access…