Advanced SQL: Window Functions & CTEs

Every data engineering interview loop eventually hands you a problem that plain GROUP BY cannot solve: rank each customer's orders without collapsing the rows, compute a 7-day moving average without a self-join, find the top 3 products per category, walk a manager-report hierarchy of unknown depth, or turn a table of session start/end timestamps into a count of distinct sessions. These are not exotic edge cases — they are the default shape of "give me one hard SQL question" in a screen for a data engineering, analytics engineering, or backend role, precisely because they cannot be solved by memorizing SELECT ... FROM ... WHERE ... GROUP BY and instead require a working mental model of how a window function actually executes relative to the rest of the query, and how a recursive CTE actually iterates.

What separates a strong answer from a weak one on these problems is rarely syntax recall. It's whether the candidate can explain why the query works: why RANK() and DENSE_RANK() diverge on ties and ROW_NUMBER() never does; why a running total written with RANGE instead of ROWS can silently produce the wrong number when the ORDER BY column has duplicates; why a recursive CTE needs an anchor and a termination condition or it will spin forever; why a gaps-and-islands solution using LAG is O(n) while a naive self-join is O(n²). A weak answer gets the right result on the sample data by pattern-matching a memorized template; a strong answer can explain what changes if the input has ties, nulls, or an unbounded hierarchy, and can predict how the query will behave before running it. This subject builds that mental model from the ground up, assumes you already know how a query is logically executed (covered in sql-mental-model-and-query-execution), and stops short of indexing and execution-plan concerns, which belong to sql-query-optimization-and-indexing. The case study case-study-sql-interview-gauntlet puts everything here under interview time pressure across a set of full problems; this subject is where you build the individual tools first.