LeetCode SQL Problems: 249 Questions With Solutions

One table, a WHERE clause and an ORDER BY — the shape most of the Easy problems reduce to.

This is the base case, and it is worth getting exactly right because every other shape is built on top of it. The clauses are evaluated in a fixed order — FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT — and that order explains most of the errors people hit on the way in. An alias defined in the SELECT list is invisible to WHERE, because WHERE ran first, but it is visible to ORDER BY, which ran last. LIMIT applies after the ordering, so "the second highest" is LIMIT 1 OFFSET 1 rather than LIMIT 2, and wrapping that query in an outer SELECT is the standard way to return NULL instead of an empty result when there is no second row. DISTINCT applies to the whole select list rather than to the column it happens to sit in front of, so adding one more column to the projection can silently un-deduplicate the result.

Collapse rows into one row per group, then filter the groups.

GROUP BY replaces the rows with one row per distinct value of the grouping key, and every column in the SELECT list then has to be either part of that key or wrapped in an aggregate — anything else is a value picked arbitrarily out of the group, which strict engines reject and lenient ones answer wrongly without telling you. The clause that is misused most is HAVING: WHERE filters rows before the grouping happens, HAVING filters groups after it, so "customers with more than one order" is a HAVING and "orders placed in 2024" is a WHERE. Both belong in the same query more often than not, because filtering the rows first leaves less to group. The other distinction worth memorising is COUNT(*) against COUNT(column): the first counts rows, the second counts rows where that column is not NULL, and on the outer side of a LEFT JOIN those two numbers are different by exactly the amount that matters.

Join a table to itself so that two of its rows can be compared side by side.

SQL compares columns within a row and never rows with each other, so any question of the form "this row against that row" has to put both rows on one line first — which is all a self-join does. The mechanics are just aliases: name the table twice and let the join condition say how the two copies relate, whether that is e.managerId = m.id for a hierarchy, b.id = a.id + 1 for the next row, or ABS(a.x - b.x) = 1 for a neighbour. Two things go wrong reliably. An equality self-join matches every row with itself, so a comparison on the key (a.id < b.id) is usually needed both to drop those self-pairs and to keep each real pair once instead of twice. And joining consecutive rows on id + 1 is only correct if the ids really are consecutive — the moment rows can be deleted, the gaps break it silently, and LAG over an explicit ordering is the honest version. Three-way self-joins are the shape behind "the same value three times in a row".

Rank, number or accumulate within a group without collapsing the rows.

A window function computes over a set of rows related to the current one and, unlike an aggregate, leaves the rows where they are. That is the entire difference: GROUP BY department gives you one row per department, while OVER (PARTITION BY department) gives you every row with its department's answer attached. The three ranking functions differ only in how they treat ties, and picking the wrong one is the most common wrong answer on this track — ROW_NUMBER gives 1, 2, 3, 4 and breaks ties arbitrarily; RANK gives 1, 2, 2, 4, skipping after a tie; DENSE_RANK gives 1, 2, 2, 3, which is what "the top three salaries, ties included" actually means. The mechanical rule to carry into the interview is that a window function cannot appear in WHERE or HAVING, because both are evaluated before the window is: the ranked query has to be wrapped in a subquery or a CTE and filtered from outside. SUM(...) OVER (PARTITION BY x ORDER BY d) is a running total, and LAG and LEAD reach the previous or next row directly, which beats a self-join on id whenever the ids might have gaps.

A subquery that mentions the outer row, and therefore runs once per outer row.

An ordinary subquery is evaluated once and its result substituted in. A correlated one refers to a column of the query around it, so it is conceptually re-evaluated for every outer row — and that is what lets it express "…than any other row in its own group". Written out, it reads almost exactly like the sentence in the problem statement, and it keeps ties, which an ORDER BY with LIMIT 1 silently discards. EXISTS is the same construction with the result thrown away: it stops at the first matching row, so it is the right form when the question is only whether something exists, and NOT EXISTS is the anti-join that keeps working when the inner column is nullable, which NOT IN does not. The cost is real, because the inner query runs per row rather than once, so the window-function form of the same question is usually the faster one — both are accepted, and the correlated version is usually the one you can write correctly first.

Compare, bucket and difference dates as dates, not as the strings they print as.

A date is its own type, and the first rule is not to compare it as text: '2024-9-1' and '2024-09-01' are the same day and different strings. Adding an interval to a column is how "the day after" is expressed, and joining on that expression is how every consecutive-day problem is written. This is the one place the dialects visibly diverge — MySQL writes d + INTERVAL 1 DAY, PostgreSQL writes d + INTERVAL '1 day', Oracle writes d + 1 — while everything else on this page is the same in all of them. Keep the arithmetic on the constant side of a comparison wherever you can: a half-open range over the raw column can use an index and a function wrapped around the column cannot, and the wrapped form also changes the type on one side of the comparison. Grouping by month means truncating the date to the month and grouping on the truncated value, and a "within the last 30 days" filter is half-open for the same reason a month bucket is — the closed form counts the boundary day twice.

NULL means unknown — not zero, not empty — and most wrong answers on this track are made of it.

Every comparison against NULL evaluates to unknown, and a WHERE clause keeps only the rows that evaluate to true, so referee_id <> 2 throws away every row whose referee_id is NULL instead of keeping it. Those rows have to be asked for explicitly with IS NULL, or given a stand-in value with COALESCE. The same rule wrecks NOT IN: if the subquery returns even a single NULL, then x NOT IN (…) is unknown for every x and the query returns nothing at all — which is why the anti-join, a LEFT JOIN with an IS NULL check on the right-hand side, is the safe way to write "customers who never ordered". The aggregate side has the mirror image of the problem: SUM and AVG ignore NULLs entirely, so an average over a column of NULLs is NULL rather than zero, and COUNT(column) skips them while COUNT(*) does not. On the outer side of a LEFT JOIN that difference is the answer: COUNT(right.id) counts real children and COUNT(*) counts one for a parent that has none.

Turn rows into columns, and conditions into numbers, by putting CASE inside an aggregate.

CASE is an expression, so it can go anywhere a column can — including inside an aggregate, which is where it stops being a formatting device and becomes a technique. SUM(CASE WHEN cond THEN 1 ELSE 0 END) counts the rows in a group that satisfy a condition, and dividing that by COUNT(*) gives the group's rate, which ROUND then trims to whatever precision the problem asked for. MAX(CASE WHEN key = 'x' THEN value END) moves one value per key into a column of its own, which is the pivot from tall to wide. The asymmetry between those two is the thing to remember: use ELSE 0 when you are counting, and omit ELSE entirely when you are pivoting, because a zero would compete with the real value inside the MAX while a NULL is ignored by it. The same expression covers the problems that ask for a computed label rather than a stored one, and inside an UPDATE it is how a column is rewritten in both directions at once — something no pair of sequential UPDATE statements can do without a temporary value.