SQL comes up in nearly every backend loop and in most data-adjacent frontend ones. The questions repeat: a handful of traps make up most of the round, and interviewers grade the mechanism behind your answer, not the keyword. This list comes from the rubric behind our SQL module and real loop reports.
At a glance, what each band is expected to clear:
| Band | Typical question | What it filters |
|---|---|---|
| junior | INNER vs LEFT JOIN, WHERE vs HAVING, IS NULL | can you read and write basic queries |
| middle | the NOT IN + NULL trap, the join-filter trap, GROUP BY rules | do you know where queries silently lie |
| senior | logical execution order, window frames, running totals | can you explain why, not recite |
The junior floor
These decide whether the conversation continues. You should answer them without pausing.
INNER JOIN vs LEFT JOIN
INNER keeps only matching rows. LEFT keeps every row of the left table and fills the right side with NULLs where nothing matched.
Follow-up "Where do the NULLs show up, and what happens if you filter on a right-table column afterwards?" That question quietly promotes the interview to the middle band; the answer is the join-filter trap below.
WHERE vs HAVING
WHERE filters rows before grouping, HAVING filters groups after aggregation. The cleanest proof you understand it fits in one line:
SELECT user_id, COUNT(*) FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5; -- no WHERE can express this
Why WHERE col = NULL returns nothing
NULL compares as UNKNOWN to everything, including another NULL, and WHERE keeps only TRUE rows. The operator is IS NULL. Related and often asked together: COUNT(*) counts rows, COUNT(col) skips NULLs.
Where middles get separated
The NOT IN + NULL trap
Trap one NULL in the subquery silently empties your whole result.
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM banned);
-- returns ZERO rows if banned.user_id contains a single NULL
Every comparison against that NULL is UNKNOWN, the chain never becomes TRUE, and WHERE drops everything. Interviewers plant this deliberately.
Right rewrite with NOT EXISTS, which handles NULLs correctly. "Delete the NULLs from the table" is not the answer they are waiting for.
The join-filter trap
Trap a right-table predicate in WHERE turns your LEFT JOIN back into an INNER JOIN.
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid'; -- WRONG: NULL rows fail this test and vanish
-- RIGHT: keep the predicate in the join condition
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid';
The credit goes to the why: WHERE runs after the join, and the NULL-filled rows fail any comparison.
GROUP BY discipline
Standard SQL requires every non-aggregated SELECT column to appear in GROUP BY. Mentioning that MySQL historically allowed skipping it, and what ONLY_FULL_GROUP_BY changed, reads as production experience. Same cluster: UNION dedupes and pays for it, UNION ALL does not; defaulting to ALL and deduping only when the data demands it is the senior instinct in one line.
What the senior band sounds like
Senior SQL questions stop being about syntax and start being about the logical execution order:
FROM / JOIN -> WHERE -> GROUP BY -> aggregates -> HAVING
-> window functions -> SELECT -> DISTINCT -> ORDER BY
Almost every "why can't I do X" puzzle resolves against that list. Why can't WHERE see a SELECT alias? Projection runs later. Why can't a window function sit in WHERE? It computes after HAVING. To filter on a ROW_NUMBER(), wrap it in a subquery or CTE.
The running-total question
Follow-up "give me a running total per user" reliably splits senior from middle, because the trap is the default window frame.
SUM(amount) OVER (ORDER BY day) -- RANGE frame: tied days share one value
SUM(amount) OVER (ORDER BY day
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) -- true row-by-row total
With the default RANGE frame, rows that tie on the ORDER BY key share one frame end, so the total jumps by whole groups. Knowing ROW_NUMBER vs RANK (gaps after ties) vs DENSE_RANK (no gaps) belongs to the same cluster.
What the grader listens for
We grade SQL answers against a written rubric, and it weighs mechanisms over keywords. Naming "three-valued logic" earns nothing by itself; showing that a NULL turned your NOT IN into an empty set earns the criterion.
Red flags treating NULL like a comparable value, claiming WHERE and HAVING are interchangeable, and "fixing" the join-filter trap by switching to INNER JOIN without noticing that was the bug. Any of these caps the score regardless of fluency.
The free notes for this topic go from the junior floor to the senior signals: SQL fundamentals. The diagnostic will tell you which band you clear today.