SQL — the queries they actually ask

Many backend interviews include a 15-min SQL round. The shapes that come up: top-N per group, running totals, joins with missing rows, "did this user do X within 7 days of Y."

The on-ramp: what a query even is

If you have never written SQL, start here. A database is a collection of tables. A table is a grid: each horizontal row (also called a "record") is one thing — one order, one user — and each vertical column (also called a "field") is one attribute of that thing, with a fixed type (number, text, date). A query is a single sentence that describes the rows you want back; the database figures out how to fetch them. You almost never tell it "loop over the file" — you describe the result and let the engine plan.

Concretely, picture a two-table world. users(id, name) and orders(id, customer_id, amount). The customer_id in orders points back at a users.id — that is how the two tables are linked. Almost every interview question is some combination of: pick columns, filter rows, link tables, group-and-count, sort, and limit.

The six clauses, defined

A SELECT statement is built from a small set of clauses. Each does exactly one job:

• SELECT — which columns come back (and computed/aggregated values). SELECT * means "every column."
• FROM — which table(s) the rows come from.
• WHERE — filter individual rows before any grouping. Keeps rows matching a condition.
• GROUP BY — collapse rows that share a value into one summary row per group, so aggregates can run per group.
• HAVING — filter the groups after grouping/aggregation (this is WHERE's post-aggregation cousin).
• ORDER BY — sort the final rows (ASC ascending, the default; DESC descending).
• LIMIT n — keep only the first n rows after sorting (some engines spell it FETCH FIRST n ROWS or TOP n).

A tiny end-to-end example reading them all at once. Given orders with rows (1, A, 90), (2, A, 40), (3, B, 50), (4, B, 60), (5, B, 20):

SELECT   customer_id, SUM(amount) AS total
FROM     orders
WHERE    amount > 25
GROUP BY customer_id
HAVING   SUM(amount) > 100
ORDER BY total DESC
LIMIT    10;
-- WHERE drops the (5,B,20) row first.
-- Groups: A -> 90+40=130, B -> 50+60=110.
-- HAVING keeps both (both > 100). ORDER BY total DESC.
-- Result:  A | 130
--          B | 110

The logical order of execution

You write the clauses in one order, but the database runs them in another. Knowing the real order explains almost every confusing error and is a favourite interview probe. The logical pipeline is:

• 1. FROM / JOIN — assemble the raw row set from the tables.
• 2. WHERE — drop rows that fail the row-level condition.
• 3. GROUP BY — bucket the surviving rows into groups.
• 4. HAVING — drop whole groups that fail the group-level condition.
• 5. SELECT — compute the output columns (this is when aliases and window functions are created).
• 6. ORDER BY — sort the result.
• 7. LIMIT — cut to the first n.

Two consequences fall straight out of this list. First, WHERE runs before grouping, so it cannot reference an aggregate like SUM() — that's HAVING's job (step 4, after grouping). Second, an alias you define in SELECT (step 5) is not visible in WHERE (step 2) — it does not exist yet — which is the exact reason the top-N pattern wraps rn in a subquery. ORDER BY (step 6) can use a SELECT alias, because it runs after.

JOINs, with a tiny example of each

A JOIN stitches two tables together row-by-row using an ON condition (usually "this table's foreign key equals that table's id"). The four types differ only in which unmatched rows they keep. Use these two tiny tables for every example below — users(id, name) = (1,Ana), (2,Bo), (3,Cy) and orders(id, customer_id) = (10, 1), (11, 1), (12, 2). Note user 3 (Cy) has no orders, and every order belongs to a real user.

SELECT u.name, o.id
FROM users u
INNER JOIN orders o
  ON o.customer_id = u.id;
-- Ana,10  Ana,11  Bo,12
-- Cy is dropped (no orders).

SELECT u.name, o.id
FROM users u
LEFT JOIN orders o
  ON o.customer_id = u.id;
-- Ana,10  Ana,11  Bo,12
-- Cy,NULL  <- kept, right side NULL

SELECT u.name, o.id
FROM users u
RIGHT JOIN orders o
  ON o.customer_id = u.id;
-- Ana,10  Ana,11  Bo,12
-- (every order had a user, so no NULLs here)

SELECT u.name, o.id
FROM users u
FULL OUTER JOIN orders o
  ON o.customer_id = u.id;
-- Ana,10  Ana,11  Bo,12  Cy,NULL
-- (orphan orders would show NULL,id)

Aggregates, subqueries, CTEs, and indexes

A few more terms you'll hear in every round, defined inline:

• Aggregate function — a function that takes many rows and returns one value: COUNT(*) (how many rows), SUM(x), AVG(x), MIN(x), MAX(x). With GROUP BY they run once per group.
• Subquery — a SELECT nested inside another query, used either as a value (WHERE amount > (SELECT AVG(amount) FROM orders)), a list (WHERE id IN (SELECT ...)), or a derived table in FROM (the (...) t in the top-N pattern).
• CTE / WITH — a "common table expression": a named, temporary result you define up front with WITH name AS (SELECT ...), then reference by name below. Same power as a subquery but far more readable, and you can chain several. Example: WITH ranked AS (SELECT ..., ROW_NUMBER() OVER(...) rn FROM orders) SELECT * FROM ranked WHERE rn <= 3; — the exact top-N pattern, rewritten readably.
• Window function (briefly) — like an aggregate but it does not collapse rows; it computes across a "window" of related rows while every row survives. ROW_NUMBER(), RANK(), SUM() OVER(...), LAG()/LEAD() are the common ones. This lesson's headline trick is a window function.
• Index — a sorted lookup structure (think the index at the back of a book) the database keeps on one or more columns so it can find matching rows without scanning the whole table. Speeds up reads on indexed columns; slightly slows writes.
• Primary key — the column(s) that uniquely identify a row (e.g. users.id); automatically unique and indexed. Foreign key — a column that references another table's primary key (e.g. orders.customer_id → users.id), enforcing that the pointed-to row exists.
• NULL — the marker for "unknown / no value." It is not zero and not an empty string. Crucially, NULL = NULL is not true (it's "unknown"), so you must test with IS NULL / IS NOT NULL, never = NULL.

-- top 3 orders per customer by amount
SELECT *
FROM (
  SELECT customer_id, id, amount,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY amount DESC) AS rn
  FROM orders
) t
WHERE rn <= 3;

The classic interview queries

Three questions show up so often they're almost a handshake. Memorise the shape of each — and be ready to explain why.

SELECT salary
FROM (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk = 2;       -- swap 2 for any N
-- The old-school trick: ORDER BY salary DESC LIMIT 1 OFFSET 1
-- (skip the top 1, take the next) — but ties can fool it.

SELECT email, COUNT(*) AS n
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
-- e.g.  a@x.com | 3
--       b@x.com | 2

SELECT u.*
FROM users u
LEFT JOIN orders o ON o.customer_id = u.id
WHERE o.id IS NULL;
-- returns Cy (had no orders).
-- NOT IN (SELECT customer_id FROM orders) also works,
-- but breaks if customer_id contains a NULL — prefer this.

Pitfalls they probe for

Three traps account for most "gotcha" follow-ups:

• WHERE vs HAVING — WHERE filters individual rows before grouping and cannot see aggregates; HAVING filters groups after aggregation and is the only place an aggregate condition (COUNT(*) > 1) can live. Putting SUM(x) > 100 in WHERE is an error.
• NULL comparisons — anything compared to NULL with =, !=, <, etc. yields "unknown," which behaves like false in a filter, so the row silently vanishes. Use IS NULL / IS NOT NULL. This is also why NOT IN (subquery) returns zero rows if the subquery contains a single NULL.
• N+1 queries — in app code, fetching a list (1 query) then looping to fetch each item's details (N more queries) means N+1 round-trips. Replace the loop with one batched query (WHERE id IN (...) or a JOIN). It's the single biggest perf killer in ORM-heavy code, and an index won't fix it — the cost is the number of round-trips, not the per-query speed.

Stop reading, start querying — this runs a real Postgres engine compiled to WebAssembly, right here in the page: