Advanced SQL is not a longer query with more joins bolted on. It is expressing "the top 3 highest earners per department" in one declarative window-function query instead of five nested subqueries, knowing exactly when RANK and ROW_NUMBER disagree on tied values, and structuring genuinely complex logic with CTEs so the next person reading the query — including future you — can follow it.
What "Advanced" Actually Covers
Window functions with OVER (PARTITION BY ... ORDER BY ...): RANK, DENSE_RANK, ROW_NUMBER, LAG/LEAD for comparing a row to the previous or next row in a sequence, and running/cumulative aggregates like a running SUM or a moving average.
Common table expressions, both single-use CTEs for readability and recursive CTEs for hierarchical data (an org chart, a category tree). Self-joins for same-table comparisons. Reading and predicting the output of a query that combines several of the above, rather than writing each in isolation.
What Still Trips People Up
- Choosing ROW_NUMBER when RANK or DENSE_RANK was the correct tool for ties, silently returning the wrong count of "top N" rows whenever two values are equal
- Forgetting PARTITION BY inside OVER(), which computes the window function across the entire result set instead of per group
- A recursive CTE with no terminating condition, or a data set with an actual cycle in it, running until it hits the engine's recursion limit
- Assuming a specific engine's CTE materialization behaviour is universal, then being surprised when the same query pattern performs very differently — or in edge cases returns a different result — on a different engine
- A self-join with an ambiguous or missing alias, producing a Cartesian-adjacent result that is technically valid SQL and practically meaningless
The theme repeats from the intermediate tier: these tools solve real, specific problems well, and each one has a specific, well-known way to get subtly wrong — none of which produce a syntax error at the point the mistake was actually made.
What to Put on a CV at This Level
"Advanced SQL: window functions (RANK/ROW_NUMBER/LAG/LEAD), CTEs including recursive, self-joins" is accurate and specific, and it will get tested with a scenario — a small table and a tie in the data — not a request to define what a window function is.
What would overstate it: "SQL expert" on the strength of one working window-function query. Expert level, covered next, is about the engine underneath the query — isolation levels, locking, execution plans — a different and harder kind of question than query correctness alone.
The Next Rung
Expert SQL moves past writing correct queries into reasoning about what happens when many queries run against the same data at once: transaction isolation levels and the specific read anomalies (dirty read, non-repeatable read, phantom read) each one prevents or allows, how to read an actual execution plan to confirm an index is being used instead of assuming it, and how foreign-key cascade behaviour (CASCADE, SET NULL, RESTRICT) affects data integrity under concurrent writes.
The SQL Test is scenario-based across 30 questions spanning fundamentals through joins, aggregation and correctness traps, which places its ceiling around the advanced tier rather than testing isolation levels directly — still a useful check even for someone who works with concurrency day to day. The Java Test is a common pairing for roles where the same person owns both the query layer and the application logic calling it.
The Real Signal Employers Are Screening For
A job ad asking for "advanced SQL" or "strong analytics SQL" is usually not testing whether you know window functions exist — it is testing whether you can look at a small sample table with a tie in it and correctly predict which rows a RANK-based query returns versus a ROW_NUMBER-based one. That is a narrow, specific, easy-to-verify claim, which is exactly why it makes a good screening question.
It is also where CV claims and actual ability diverge the most, because "I've used CTEs" is an easy sentence to write and a much harder thing to demonstrate under a scenario question. Naming the specific tools — RANK vs ROW_NUMBER, recursive CTEs, self-joins — and being able to explain a tie-handling difference on request reads as far more credible than the bare claim "advanced SQL".