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…