Advanced Projects
Prerequisites: Level 3 — Advanced
Build these using window functions, recursive queries, indexing, transactions, and the production practices from Level 3.
1. Analytics Dashboard Queries
On a dataset with at least 100,000 rows (generate one with
generate_series in PostgreSQL or a recursive CTE), write queries for
running totals, month-over-month growth, rolling 7-day averages, top-N per
group, and customer retention by signup month. Measure each with
EXPLAIN ANALYZE, add indexes where they help, and record before-and-after
timings.
2. Banking Ledger
Design a double-entry ledger: accounts and transfers, where every transfer debits one account and credits another inside a transaction. Enforce that balances can't go negative, prevent lost updates under concurrency (test it with two sessions), and write a query that reconstructs any account's balance at any point in time.
3. Schema Migration Project
Take a database with a design flaw (for example, a comma-separated list of tags in one column) and write a series of versioned migrations that fix it without downtime: add the new tables, backfill data, switch reads, then drop the old column. Include a rollback for each step.