SQL
The queries you get asked to write, how to read a query plan, and a six-week ladder that makes both automatic.
This page is the practice half of the database work: the SQL you write in an interview and on the job, plus the habit of reading a query plan before you guess. Read it with psql open on Ledgerline, because SQL is learned by typing.
The theory behind these queries is on the DBMS page.
Priority for SDE-1
Joins, aggregation, CTEs, window functions, EXPLAIN basics and one safe migration are mandatory. Planner internals, partitioning, materialised views and tuning at scale wait until you have an offer.
SDE-1 scope vs SDE-2 scope
| Area | SDE-1 must | SDE-2 later |
|---|---|---|
| Reads | WHERE, ORDER BY, LIMIT, null semantics | Lateral joins, complex plan shapes |
| Joins | Inner, left, anti-join, self join | Semi-join and hash join plans |
| Aggregation | GROUP BY, HAVING, conditional counts | Grouping sets, rollup, cube |
| CTEs | Readable steps, one recursive query | Materialisation control |
| Windows | row_number, rank, lag, running totals | Frames, gaps and islands at scale |
| Writes | Upsert, transactions, savepoints | Bulk load, logical replication |
| Performance | EXPLAIN ANALYZE, index choice, N+1 | Planner costing, statistics |
| Schema change | Additive migration with a backfill | Online rewrites, expand and contract |
| Analytics | Group and window reports | Materialised views, partition pruning |
Start with one slow query
Take the endpoint that lists transfers for an account and run it with EXPLAIN (ANALYZE, BUFFERS). Write the top node's estimated rows next to its actual rows.
If those two row numbers differ by more than ten times, you have found the bug. That habit covers most of what an interviewer means by "can you debug a query".
Roadmap
Stage 1: syntax to joins (weeks 1 to 2)
Four sessions a week of 45 minutes, inside the fundamentals hours. Finish every SQLBolt lesson in a sitting, then move to joins on the pgexercises dataset.
Stage 2: the interview core (weeks 3 to 4)
Aggregation, CTEs and the problems on LeetCode SQL 50, timed at 20 minutes each. Re-solve failures after three days, the way the revision deck works.
Stage 3: windows and plans (weeks 5 to 6)
Window functions, EXPLAIN, and one real migration on Ledgerline. Planner internals come after the offer, on the DBMS page.
Order of execution
You write SELECT first and the database runs it almost last. The order is from, join, WHERE, GROUP BY, HAVING, window functions, SELECT, DISTINCT, ORDER BY, LIMIT.
Two consequences matter in an interview. You cannot filter on a SELECT alias in WHERE, because the alias does not exist yet. And WHERE removes rows before grouping while HAVING removes groups after it, so a filter in the wrong clause changes your numbers.
Joins
An inner join keeps matching rows from both sides. A left join keeps every left row and fills nulls where the right side is missing.
The classic mistake is filtering the right table in WHERE. WHERE b.id IS NOT NULL after a left join silently makes it an inner join, because null rows fail the test. Put the condition in ON to keep the left rows.
The anti-join finds rows with no match: LEFT JOIN ... WHERE b.id IS NULL, or NOT EXISTS (SELECT 1 ...). A self join is the same table twice with two aliases, which answers "employees who earn more than their manager".
GROUP BY and HAVING
Every selected column must be aggregated or listed in GROUP BY. Postgres relaxes that when the column depends on the primary key, which is worth saying out loud.
count(*) counts rows and count(column) skips nulls, which answers half the "why is my count wrong" questions. FILTER (WHERE ...) gives several conditional counts in one pass, and HAVING filters the groups afterwards.
Subqueries vs CTEs
A scalar subquery returns one value for the select list. EXISTS stops at the first match and pairs well with an index, which makes it the better "does this exist" check.
A correlated subquery runs once per outer row. That is an N+1 query written in SQL, and the usual reason a tidy query takes four seconds.
A CTE with WITH names a step so the query reads top to bottom. Postgres inlines it since version 12, and MATERIALIZED forces a separate pass when you need one. Use a recursive CTE for a hierarchy.
Window functions
A window function calculates across related rows without collapsing them. GROUP BY returns one row per group, while a window returns every row with an extra column.
The four you will use are ROW_NUMBER(), RANK() for ties, LAG() and LEAD(), and SUM() OVER (ORDER BY ...) for a running total. PARTITION BY account_id ORDER BY created_at is a running balance per account, and WHERE rn = 1 over a ROW_NUMBER() deduplicates.
Watch the frame. With an ORDER BY and no explicit frame, tied rows share the running total, so two transfers in the same second get the same figure. Use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for one row at a time.
Set operations
UNION removes duplicates and sorts, UNION ALL keeps everything and is faster, so default to UNION ALL. INTERSECT and EXCEPT answer "in both" and "in one but not the other". All four need matching column counts, and one ORDER BY applies to the whole result.
Upserts
INSERT ... ON CONFLICT (key) DO NOTHING skips an existing row, DO UPDATE SET ... overwrites it, and RETURNING gives you the row either way. The conflict target needs a unique index, and that index is the concurrency control: one of two simultaneous inserts waits, then takes the conflict path.
In Ledgerline this is the whole idempotency mechanism. Insert the key first, and on conflict return the stored result instead of applying the transfer again.
Transactions in SQL
BEGIN starts a transaction, COMMIT makes it durable, ROLLBACK throws it away, and SET TRANSACTION ISOLATION LEVEL applies to the current transaction only. SAVEPOINT with ROLLBACK TO lets a batch keep the good rows and skip the bad ones.
SELECT ... FOR UPDATE locks the rows you read. FOR UPDATE SKIP LOCKED lets a worker take the next unlocked job, which is the outbox worker, and worth writing from memory. Keep transactions short, with no HTTP calls inside.
Reading EXPLAIN ANALYZE
Read the plan from the innermost node outward, usually bottom-up. Compare rows= with actual rows=, because a large gap is where the planner guessed wrong.
cost is in arbitrary units and only comparable inside one plan. actual time is in milliseconds, and with loops=N it is the average per loop, so multiply before you blame a node.
Look for a sequential scan on a large table, a nested loop with many outer rows, and a sort that spills to disk. BUFFERS shows cache hits against disk reads.
Choosing an index
Index the equality columns first, then the range column, because a composite index only helps from its leftmost column. Check the plan before and after, then query pg_stat_user_indexes for indexes with no scans and drop them.
Debugging a slow query
Reproduce it with real parameter values. The plan for a parameter matching one row is not the plan for one matching two million.
Run EXPLAIN (ANALYZE, BUFFERS) and find the node where actual rows explode past the estimate.
Run ANALYZE, and look for correlated columns the planner treats as independent.
Change one thing, re-measure, and put the before and after numbers in the pull request. That table is your interview story.
Migrations that do not lock the table
Add before you remove. A new nullable column costs almost nothing, a batched backfill keeps locks short, and the constraint goes on last. A constant default is cheap in modern Postgres, but a volatile default rewrites every row.
CREATE INDEX CONCURRENTLY avoids blocking writes and cannot run inside a transaction block. Renaming is a three-deploy job: add the new column, write both, backfill, switch reads, then drop the old one.
Analytical queries (SDE-2 later)
GROUPING SETS, ROLLUP and CUBE give several aggregation levels in one pass. A materialised view stores the result, and REFRESH CONCURRENTLY keeps readers unblocked if the view has a unique index.
Partitioning only helps when the query filters on the partition key, because then the planner skips the other partitions. An aggregation that spills to disk is a work_mem problem, not an index problem.
The six-week practice ladder
Four sessions a week of 45 minutes, inside the fundamentals hours, starting in week 1 of the plan. Each week has an exit test you can fail honestly.
| Week | Focus | Exercises | Exit test |
|---|---|---|---|
| 1 | Filtering and nulls | SQLBolt lessons 1 to 6, pgexercises basic | Write five filters and predict the null behaviour |
| 2 | Joins | pgexercises joins, SQL 50 basic joins | The anti-join for "customers with no orders", two ways |
| 3 | Aggregation | pgexercises aggregation, SQL 50 basic aggregate functions | One query with three conditional counts using FILTER |
| 4 | Subqueries and CTEs | SQL 50 subqueries and sorting, one recursive query | A correlated subquery rewritten as a join, with the plan |
| 5 | Window functions | SQL 50 advanced and window functions, every example in the Postgres tutorial | Top three transfers per account, and a running balance |
| 6 | Plans and migrations | EXPLAIN on Ledgerline, one concurrent index | A before and after table with real timings |
Type every query by hand, and do not paste from a solution. Timebox each problem to 20 minutes, then rebuild the failed ones from scratch two days later. The SQL 50 set is the closest thing to an interview loop, so do it timed.
Resources
Learn
- SQLBolt: interactive lessons, the fastest first pass through
SELECT. SDE-1 must. - PostgreSQL: joins between tables: the canonical join semantics. SDE-1 must.
- PostgreSQL: window functions: read it once, then type every example. SDE-1 must.
- PostgreSQL: WITH queries: CTEs, materialisation and recursion. SDE-1 must.
- PostgreSQL: INSERT:
ON CONFLICTandRETURNING, exact syntax. SDE-1 must. - PostgreSQL: using EXPLAIN: how to read a plan without guessing. SDE-1 must.
Practice
- pgexercises: 80+ exercises on one dataset, with explained answers. SDE-1 must.
- LeetCode SQL 50: interview-shaped problems, best done timed. SDE-1 must.
Long-form
- Use The Index, Luke: the indexing book, chapters 1 to 3 cover what SDE-1 needs. SDE-1 nice.
- Zero-downtime Postgres migrations: the hard parts: what actually locks a table in production. SDE-1 nice.
Questions to answer out loud
- In what order does a
SELECTrun, and why can you not filter on an alias inWHERE? - Write the anti-join for "customers with no orders" two ways.
- Why does a condition on the right table in
WHEREbreak a left join? - What is the difference between
WHEREandHAVING? - Rewrite a correlated subquery as a join. When is
EXISTSbetter? - Write the query for the top three transfers per account.
- What is the difference between
UNIONandUNION ALL, and which do you default to? - What does
ON CONFLICTneed to work, and what doesRETURNINGgive you? - Point at the expensive node in this
EXPLAINoutput, and say why. - How do you add a
NOT NULLcolumn to a fifty-million-row table without downtime?
SDE-2 later
- Why does the planner pick a nested loop over a hash join, and when is its estimate wrong?
- What does
CREATE INDEX CONCURRENTLYdo differently, and why can it fail? - When does a materialised view beat a query, and how do you refresh it without locking readers?
How this shows up in your projects
- Ledgerline: the idempotency insert, the balance query, and the outbox worker reading with
SKIP LOCKEDare three queries to write from memory. - Ledgerline's reconciliation is an aggregation over the journal, so a slow one gives you a real query to tune and a real before and after table.
- Logsmith: consumer offsets and lag are two queries, and retention by age is a delete with a predicate you can explain.
Common mistakes
- Read the plan before you add an index.
- Default to
UNION ALL, and toNOT EXISTSfor anti-joins. - Put left-join conditions in
ON, notWHERE. - Timebox practice problems, then rebuild the ones you failed.
- Add columns before you drop them, and backfill in batches.