Intermediate SQL is where most real reporting queries actually live: LEFT JOIN across several tables, subqueries, and aggregation with more than one grouping level. It is genuinely productive work — and it is also where two specific, well-documented traps live: a WHERE clause in the wrong place quietly undoing a LEFT JOIN, and a `NOT IN` subquery that can silently return nothing at all.
What "Intermediate" Actually Covers
LEFT JOIN and RIGHT JOIN across multiple tables, and knowing precisely where a condition belongs — in ON versus WHERE — to preserve the join type you intended. Subqueries in the WHERE clause, the SELECT list, and the FROM clause, plus the difference between a correlated and non-correlated subquery.
Multi-column and multi-level GROUP BY, DISTINCT versus GROUP BY for deduplication, UNION versus UNION ALL, and basic string/date functions used inside a WHERE or SELECT clause without breaking index usage on that column.
Where It Breaks
- A filter condition on the outer-joined table placed in WHERE instead of ON, silently converting a LEFT JOIN back into an INNER JOIN and dropping every unmatched row
- `NOT IN (SELECT ...)` returning zero rows for the entire query because the subquery produced a single NULL, with no error to explain why
- A join that fans out — one row on one side matching several rows on the other — silently inflating a COUNT or SUM computed in the same query
- UNION silently deduplicating rows the query actually needed to keep, because UNION ALL (which keeps duplicates) was the correct choice
- A function wrapped around an indexed column in WHERE (`WHERE YEAR(order_date) = 2026`), which can prevent the engine from using an index on that column, turning a fast lookup into a full table scan
The pattern across all five: intermediate SQL mistakes are rarely syntax errors. They are queries that run, return a plausible-looking result, and are quietly wrong — either the wrong row count or the wrong performance profile — which is exactly why reading and predicting behaviour matters more than reciting clause definitions at this level.
What to Put on a CV at This Level
"Intermediate SQL: multi-table LEFT/RIGHT JOIN, correlated and non-correlated subqueries, multi-level GROUP BY, UNION vs UNION ALL" is accurate and matches what most job ads asking for "strong SQL" actually mean — building a correct multi-table report from scratch, not just running a query someone else wrote.
What would overstate it: claiming "advanced SQL" on the strength of writing a working LEFT JOIN. Advanced work, covered next, is specifically about window functions and CTEs — a distinct, testable skill set most intermediate SQL never touches.
The Next Rung
Advanced SQL is built around window functions — RANK, DENSE_RANK, ROW_NUMBER, running totals with OVER — and common table expressions, which let you express multi-step logic (find the top N per group, compute a running average) without stacking subqueries five levels deep.
The SQL Test scores joins and query-correctness scenarios as part of its 30 questions, which is a fast way to see specifically whether the ON-versus-WHERE distinction or the NULL-subquery trap is the actual gap holding an intermediate score back. Pairing it with the Python Test is common for roles that expect both data querying and scripting.
Why Intermediate SQL Is Where Most Real Reporting Work Sits
It is worth saying plainly: intermediate is not a consolation tier. A large share of dashboards, ad-hoc reports and BI queries never touch a window function or a recursive CTE, and do not need to — the join-and-subquery toolkit here is a complete, employable skill on its own.
The honest reason to push further is that any query feeding a metric someone will act on — a revenue number, a headcount, a churn rate — eventually needs a per-group ranking or a running total that a plain GROUP BY cannot express cleanly. If that describes the work ahead, the advanced tier is worth the time. If it does not, intermediate SQL done carefully, with the NULL and join traps genuinely understood, is a complete and trustworthy skill.