Advanced SQL: subqueries, CTEs and window functions

In short

Advanced SQL is everything beyond a plain SELECT-FROM-WHERE-JOIN: queries nested inside queries (subqueries), named query blocks that can call themselves (CTEs), and functions that compute across a set of rows *without collapsing them* (window or analytic functions). Together they answer the questions business data actually asks — rank, running total, month-on-month growth, top-N per group, hierarchy traversal — in one declarative statement instead of a procedural loop. University papers and GATE (CS/DA) test nested-query semantics, correlated evaluation and output-row counting; campus tests (TCS NQT, Infosys, Wipro, Accenture, Cognizant) and product-company SQL rounds (Flipkart, Zomato, Swiggy, PhonePe, Razorpay, Zoho, Freshworks) almost always ask "Nth highest salary", "top 2 per department" and a window-function problem. Every analytics, data-engineering, BI and backend role in India writes this SQL daily against PostgreSQL, MySQL 8, Oracle, SQL Server or a warehouse.

Explained

Subqueries: kinds and where they may sit

• A subquery (nested query / inner query) is a complete SELECT enclosed in parentheses inside another statement. By result shape: scalar (one row, one column), row (one row, many columns), column/table (many rows).
• In the SELECT list — must be scalar; returns a value per outer row: (SELECT AVG(salary) FROM emp). If it returns more than one row the query fails at runtime.
• In the FROM clause — a derived table / inline view; it must be given an alias in MySQL and PostgreSQL. Useful to filter on a computed column that WHERE cannot see.
• In WHERE / HAVING — with comparison operators (= , >), with IN, EXISTS, ANY/SOME, ALL.
• In INSERT, UPDATE, DELETE and MERGE — INSERT ... SELECT, UPDATE ... SET col = (SELECT ...), DELETE ... WHERE id IN (SELECT ...).
• LATERAL / CROSS APPLY — a FROM-clause subquery that *may* reference columns of tables listed to its left; the engine runs it once per outer row. LATERAL is in PostgreSQL 9.3+, MySQL 8.0.14+, Oracle 12c+; SQL Server spells it CROSS APPLY / OUTER APPLY. It is the standard way to get "latest 3 orders of each customer".
• Uncorrelated subquery: independent of the outer query, evaluated once, result cached. Correlated: references an outer column, so conceptually re-evaluated for every outer row.

Correlated subqueries, EXISTS and the NULL traps

• EXISTS (subquery) is TRUE if the subquery returns at least one row — a semi-join. NOT EXISTS is an anti-join. The SELECT list inside EXISTS is irrelevant; SELECT 1 is the convention.
• **IN vs EXISTS:** logically interchangeable for simple membership; IN is natural for a small constant or uncorrelated list, EXISTS for a correlated existence test. Modern optimisers *unnest* (decorrelate) both into semi-joins, so the performance folklore ("EXISTS is always faster") is no longer reliable — check the execution plan.
• The NOT IN / NULL trap: if the subquery returns even one NULL, x NOT IN (...) can never be TRUE — it evaluates to UNKNOWN, so the outer row is dropped and the query returns zero rows. NOT EXISTS is NULL-safe and is the correct anti-join. This is the single most asked "tricky" question on the topic.
• **ANY / SOME and ALL:** salary > ALL (SELECT ...) means greater than the maximum; salary > ANY (...) means greater than the minimum. = ANY is equivalent to IN; <> ALL is equivalent to NOT IN (and inherits the same NULL problem). An empty subquery makes ALL TRUE and ANY FALSE.
• Relational division ("students who have taken *every* course offered") is written with double NOT EXISTS: there is no course such that the student has not taken it. A frequent GATE/theory question.
• Correlated subqueries can also appear in the SELECT list (one scalar per row) and in HAVING, where they are evaluated per group.

Common table expressions (CTEs)

• A CTE is a named temporary result set defined by WITH name (cols) AS (SELECT ...) and visible only to the statement that follows. Introduced for recursion in SQL:1999; available in Oracle 9i+, SQL Server 2005+, PostgreSQL 8.4+, MySQL 8.0+ / MariaDB 10.2+, SQLite 3.8.3+.
• Why use one: it names a step, so a long query reads top-to-bottom instead of inside-out; the same block can be referenced several times; it replaces most temp tables for read-only work.
• Several CTEs are separated by commas: WITH a AS (...), b AS (SELECT ... FROM a) SELECT .... A later CTE may reference an earlier one, not the reverse.
• CTE vs derived table: semantically the same for non-recursive cases; a derived table cannot be reused, a CTE can. CTE vs view: a view is a stored schema object, a CTE lives for one statement. CTE vs temp table: a temp table is materialised, can be indexed and persists for the session — better when a huge intermediate result is reused many times.
• Materialisation: PostgreSQL ≤ 11 always materialised a CTE (an optimisation fence); from PostgreSQL 12 a CTE used once is inlined unless you write AS MATERIALIZED. Oracle has the /*+ MATERIALIZE */ and /*+ INLINE */ hints. Treating a CTE as "automatically faster" is wrong — it is a readability device first.
• Recursive CTE (WITH RECURSIVE in PostgreSQL/MySQL/SQLite; just WITH in Oracle and SQL Server) has three parts: an anchor member, UNION ALL, and a recursive member that references the CTE itself. It iterates until the recursive member returns no rows.
  • Uses: organisation charts, bill-of-materials explosion, folder trees, road/route graphs, generating a continuous date or number series to fill gaps.
  • Runaway protection: SQL Server stops at 100 levels by default (OPTION (MAXRECURSION n), 0 = unlimited); MySQL uses cte_max_recursion_depth (default 1000) and max_execution_time; PostgreSQL has no default limit, so a cyclic graph loops forever unless you carry a depth counter or a visited-path array and test it.
  • UNION instead of UNION ALL removes duplicates each iteration (slower, but breaks some cycles). Aggregates, ORDER BY, LIMIT and outer joins are restricted inside the recursive member.

Window (analytic) functions

• A window function computes a value over a set of rows *related to the current row* and returns one value per row — unlike GROUP BY, which collapses rows. Standardised in the SQL:1999 OLAP amendment, folded into SQL:2003; Oracle and SQL Server have had them for years, MySQL only from 8.0, SQLite from 3.25.
• Syntax: function() OVER (PARTITION BY ... ORDER BY ... frame). All three inner clauses are optional. A repeated specification can be named once: WINDOW w AS (PARTITION BY dept ORDER BY salary DESC) and then OVER w.
  • PARTITION BY splits rows into independent groups (like GROUP BY, but rows survive). Omit it and the whole result set is one partition.
  • ORDER BY orders rows *within* the partition and determines which rows precede the current one.
  • Frame selects a sliding sub-range of the ordered partition.
• Frame clause: ROWS | RANGE | GROUPS BETWEEN <start> AND <end>, where bounds are UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING, UNBOUNDED FOLLOWING.
  • ROWS counts physical rows. RANGE works on *values* of the ORDER BY key, so all peers (ties) are treated as one unit; RANGE 7 PRECEDING on a date column means an actual 7-day value window (needs a single numeric/date/interval sort key). GROUPS counts peer groups (PostgreSQL 11+, not in MySQL 8/Oracle).
  • Defaults, and the classic bug: with an ORDER BY and no explicit frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — so a running SUM gives tied rows the *same* total; with no ORDER BY at all the frame is the whole partition.
  • EXCLUDE CURRENT ROW | GROUP | TIES | NO OTHERS refines the frame (PostgreSQL 11+, SQLite 3.28+; not in MySQL 8, Oracle or SQL Server).
• Function families:
  • Ranking (no frame allowed): ROW_NUMBER() — 1,2,3,4 always distinct; RANK() — ties share a rank and leave gaps (1,2,2,4); DENSE_RANK() — ties share, no gaps (1,2,2,3); PERCENT_RANK(), CUME_DIST(), NTILE(b) — splits the partition into b near-equal buckets (quartiles, deciles).
  • Offset/value: LAG(col, n, default) and LEAD(col, n, default) reach back/forward n rows (default n = 1); FIRST_VALUE, LAST_VALUE, NTH_VALUE(col, n) FROM FIRST|LAST.
  • Aggregates as windows: SUM/AVG/COUNT/MIN/MAX ... OVER (...) for running totals, moving averages and share-of-total. DISTINCT is generally not allowed inside a window aggregate.
  • Ordered-set aggregates: PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) for a true median (interpolated) and PERCENTILE_DISC for an actual data value.
• Where a window function may appear: only in the SELECT list and ORDER BY. It is evaluated *after* WHERE, GROUP BY and HAVING, so you cannot filter on it directly — wrap the query in a CTE or derived table and filter outside. Snowflake, BigQuery, Teradata and Databricks offer QUALIFY as a shortcut; PostgreSQL, MySQL, Oracle and SQL Server do not.

Logical processing order and advanced grouping

1. FROM / JOIN → 2. WHERE → 3. GROUP BY → 4. HAVING → 5. window functions and SELECT list → 6. DISTINCT → 7. ORDER BY → 8. LIMIT / OFFSET / FETCH FIRST n ROWS WITH TIES.
2. This order explains: why a SELECT alias is unusable in WHERE but usable in ORDER BY; why window functions cannot sit in WHERE; why WHERE filters rows and HAVING filters groups.
3. GROUPING SETS, ROLLUP, CUBE (SQL:1999 OLAP; PostgreSQL 9.5+, MySQL supports WITH ROLLUP only) produce several grouping levels in one pass — subtotals and grand totals for reports. GROUPING(col) returns 1 when the column was aggregated away, which distinguishes a subtotal NULL from a real NULL.
4. FILTER (WHERE condition) on an aggregate (PostgreSQL, SQLite) is the clean form of conditional aggregation; SUM(CASE WHEN ... THEN 1 ELSE 0 END) is the portable equivalent and works in every engine.
5. Set operators UNION [ALL], INTERSECT, EXCEPT (MINUS in Oracle) combine query blocks; UNION deduplicates (usually by sorting or hashing, but the output order is not guaranteed), UNION ALL does not and is cheaper.

Performance and correctness notes

• A correlated subquery in the SELECT list repeated for several aggregates re-scans the inner table each time; one window function with PARTITION BY replaces all of them in a single pass — usually the largest practical speed-up on this topic.
• Window functions need an ordered partition, so the plan shows a Sort + WindowAgg; an index matching (partition key, order key) can remove the sort.
• Scalar subqueries in the SELECT list of a large result are a common cause of slow dashboards; rewrite as a join or a window.
• EXISTS can short-circuit on the first matching row; COUNT(*) > 0 cannot, and is the wrong idiom.
• Deterministic output needs a deterministic ORDER BY: add a unique tiebreaker column, else ROW_NUMBER() over ties may vary between runs.

Key points

• Subquery - a SELECT nested inside another statement; scalar, row or table valued.
• Derived table - a subquery in FROM; must be aliased, cannot be reused in the same query.
• Correlated subquery - references an outer column, conceptually evaluated once per outer row.
• EXISTS / NOT EXISTS - semi-join and anti-join; NULL-safe, unlike NOT IN.
• NOT IN with a NULL in the list returns no rows at all — UNKNOWN is not TRUE.
• **> ALL means greater than the maximum; > ANY** means greater than the minimum.
• Relational division - "for all" is written as double NOT EXISTS.
• CTE (WITH) - a named, statement-scoped query block; improves readability and allows reuse.
• Recursive CTE - anchor UNION ALL recursive member; stops when the recursive member yields nothing.
• Window function - returns one value per row over a related set; does not collapse rows.
• PARTITION BY / ORDER BY / frame - the three parts of the OVER clause; all optional.
• ROW_NUMBER vs RANK vs DENSE_RANK - 1,2,3,4 / 1,2,2,4 / 1,2,2,3 on tied values.
• Default frame with ORDER BY is RANGE UNBOUNDED PRECEDING TO CURRENT ROW — peers share the value.
• ROWS counts rows, RANGE counts values (peers together), GROUPS counts peer groups.
• LAG / LEAD - previous/next row value; the basis of growth, delta and gap calculations.
• LAST_VALUE needs an explicit frame ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, else it returns the current row.
• Window functions run after WHERE/HAVING - filter them in an outer query, CTE or QUALIFY where supported.
• ROLLUP / CUBE / GROUPING SETS - multi-level subtotals in one scan; GROUPING() flags the subtotal rows.

Formulas and examples

Example 1 — ranking and running totals (monthly salary in ₹). Table emp(emp, dept, salary): A1 Sales 90,000; A2 Sales 75,000; A3 Sales 75,000; A4 Sales 60,000; A5 Tech 1,20,000; A6 Tech 95,000.

SELECT emp, dept, salary,
       ROW_NUMBER()  OVER w AS rn,
       RANK()        OVER w AS rnk,
       DENSE_RANK()  OVER w AS drnk,
       PERCENT_RANK() OVER w AS prnk,
       CUME_DIST()   OVER w AS cdist,
       SUM(salary) OVER w AS run_range,
       SUM(salary) OVER (PARTITION BY dept ORDER BY salary DESC
                         ROWS UNBOUNDED PRECEDING) AS run_rows
FROM emp
WINDOW w AS (PARTITION BY dept ORDER BY salary DESC);

Sales partition, N = 4, total = 90,000 + 75,000 + 75,000 + 60,000 = ₹3,00,000:

Note the two running totals differ for the tied rows: the default RANGE frame gives both 75,000 rows ₹2,40,000, ROWS gives ₹1,65,000 and ₹2,40,000. A1's share of the department = 90,000/3,00,000 × 100 = 30%. Tech: total ₹2,15,000, average ₹1,07,500; A5 prnk = 0, cdist = 0.5.

Example 2 — third-highest salary, three ways. Distinct salaries: 1,20,000 > 95,000 > 90,000 > 75,000 > 60,000, so the answer is ₹90,000.

-- (a) correlated subquery: exactly 2 distinct salaries are higher
SELECT DISTINCT salary FROM emp e
WHERE (SELECT COUNT(DISTINCT s.salary) FROM emp s WHERE s.salary > e.salary) = 2;
-- (b) window function (handles ties correctly)
WITH r AS (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) dr FROM emp)
SELECT DISTINCT salary FROM r WHERE dr = 3;
-- (c) OFFSET-FETCH (wrong if duplicates are not removed first)
SELECT DISTINCT salary FROM emp ORDER BY salary DESC OFFSET 2 ROWS FETCH NEXT 1 ROW ONLY;

Counting greater salaries for ₹90,000: {1,20,000 , 95,000} → count 2 ✔.

Example 3 — LAG and moving average (branch revenue, ₹).

Check: (13,50,000 − 12,00,000)/12,00,000 = 0.125; (12,15,000 − 13,50,000)/13,50,000 = −0.10; (14,58,000 − 12,15,000)/12,15,000 = 0.20.

Example 4 — recursive CTE on a reporting hierarchy. staff(id, name, mgr_id): 1 Ravi NULL; 2 Priya 1; 3 Arun 1; 4 Meena 2; 5 Kiran 4.

WITH RECURSIVE tree AS (
    SELECT id, name, mgr_id, 1 AS lvl, CAST(name AS VARCHAR(200)) AS path
    FROM staff WHERE mgr_id IS NULL
  UNION ALL
    SELECT s.id, s.name, s.mgr_id, t.lvl + 1, CONCAT(t.path, ' > ', s.name)
    FROM staff s JOIN tree t ON s.mgr_id = t.id)
SELECT lvl, path FROM tree ORDER BY path;

Iterations: anchor → 1 row (Ravi, lvl 1); iteration 1 → Priya, Arun (lvl 2); iteration 2 → Meena (lvl 3, path Ravi > Priya > Meena); iteration 3 → Kiran (lvl 4); iteration 4 → 0 rows, stop. 5 rows, maximum depth 4.

Example 5 — the NOT IN trap. customer(id) = {1, 2, 3}; orders(cust_id) = {1, 2, NULL}.

• SELECT id FROM customer WHERE id NOT IN (SELECT cust_id FROM orders); → 0 rows (3 ≠ 1 AND 3 ≠ 2 AND 3 ≠ NULL → TRUE AND TRUE AND UNKNOWN = UNKNOWN).
• SELECT id FROM customer c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.cust_id = c.id); → id = 3, the correct answer.

Comparison table

Where it is used

• Banking, payments and fintech (HDFC, ICICI, SBI analytics teams, NPCI/UPI, Razorpay, PhonePe, Paytm): running balances, 30-day moving transaction volumes, fraud rules such as "more than 5 transactions in 10 minutes" written with COUNT(*) OVER (PARTITION BY card ORDER BY ts RANGE INTERVAL '10' MINUTE PRECEDING).
• E-commerce and food delivery (Flipkart, Myntra, Amazon India, Swiggy, Zomato, Meesho): top-N products per category, first and repeat order flags with ROW_NUMBER(), cohort retention, RFM deciles with NTILE(10), funnel steps with LEAD().
• Business intelligence and reporting roles: Power BI/Tableau/Metabase models push ROLLUP-style subtotals and YoY comparisons into SQL views; MIS reports for GST returns, state-wise sales and branch rankings.
• ERP, HRMS and payroll products (Zoho, Darwinbox, Keka, TCS/Infosys delivery projects): salary bands, leave-balance running totals, reporting-hierarchy explosion with recursive CTEs, bill-of-materials in manufacturing ERP.
• Data engineering (Snowflake, BigQuery, Databricks, Redshift on Indian client projects): deduplication of ingested batches with ROW_NUMBER() OVER (PARTITION BY natural_key ORDER BY load_ts DESC) = 1, slowly-changing-dimension validity dates built from LEAD(effective_date).
• Hiring screens: HackerRank/DataLemur-style SQL rounds for data analyst, analytics engineer and backend roles; GATE CS/DA and UGC-NET questions on nested-query output and tuple counts.

Interview questions

• Q: What is the difference between a correlated and a non-correlated subquery? A: A non-correlated subquery is independent and evaluated once; a correlated one references a column from the outer query, so it is conceptually evaluated for every outer row (optimisers often rewrite it as a join).
• Q: Why can NOT IN return no rows where NOT EXISTS returns rows? A: If the subquery produces a NULL, the comparison yields UNKNOWN rather than TRUE, so every outer row is filtered out; NOT EXISTS only tests for the presence of a row and is NULL-safe.
• Q: Difference between ROW_NUMBER, RANK and DENSE_RANK? A: On tied values ROW_NUMBER gives distinct sequential numbers, RANK repeats the rank and skips the next ones (1,2,2,4), DENSE_RANK repeats without gaps (1,2,2,3).
• Q: How do you find the Nth highest salary? A: DENSE_RANK() OVER (ORDER BY salary DESC) = N in a CTE, or a correlated subquery counting exactly N−1 distinct higher salaries; DENSE_RANK is preferred because it handles duplicates.
• Q: Why can't you put a window function in the WHERE clause? A: Window functions are evaluated after FROM, WHERE, GROUP BY and HAVING, so the filter must go in an enclosing query, a CTE, or QUALIFY on engines that support it.
• Q: Difference between ROWS and RANGE in a frame? A: ROWS counts physical rows, so tied rows get different frames; RANGE works on the ORDER BY values and includes all peers, which is why the default running total repeats for ties.
• Q: Why does LAST_VALUE return the current row's value? A: Because the default frame ends at CURRENT ROW; specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to see the real last value.
• Q: GROUP BY versus a window aggregate? A: GROUP BY collapses each group into one row; a window aggregate keeps every row and attaches the group value, so detail and aggregate can appear side by side.
• Q: How does a recursive CTE terminate, and how do you guard against infinite recursion? A: It stops when the recursive member returns zero rows; guard with a depth counter, a visited-path check for cyclic graphs, OPTION (MAXRECURSION n) in SQL Server or cte_max_recursion_depth in MySQL.
• Q: Is a CTE faster than a subquery? A: Usually neither — it is mainly a readability construct; PostgreSQL 12+ inlines single-use CTEs, and AS MATERIALIZED (or Oracle's MATERIALIZE hint) forces materialisation when you want it.
• Q: How would you delete duplicate rows keeping the latest? A: Number the rows with ROW_NUMBER() OVER (PARTITION BY natural_key ORDER BY created_at DESC) in a CTE and delete where the number is greater than 1.
• Q: How do you express "customers who ordered every product in a category"? A: Relational division with double NOT EXISTS — no product in the category for which that customer has no order — or compare COUNT(DISTINCT product) against the category's product count.

Common mistakes

• Using NOT IN against a nullable column instead of NOT EXISTS, and getting an empty result.
• Writing a scalar subquery in the SELECT list that can return more than one row — it passes testing on small data and fails in production.
• Trying to filter on a window function in WHERE or HAVING, or on a SELECT alias in WHERE, instead of wrapping the query.
• Forgetting that the default frame is RANGE, then reporting a running total that is identical for tied rows.
• Omitting the alias on a FROM-clause subquery (syntax error in MySQL/PostgreSQL), or omitting PARTITION BY and computing a global rank when a per-department one was asked for.
• Using ROW_NUMBER where DENSE_RANK is needed, so a tie at the Nth position silently drops a qualifying employee.
• Writing a recursive CTE with no depth guard on cyclic data, or with UNION ALL plus a self-referencing join that never empties.
• Replacing a single window pass with several correlated subqueries over the same table, multiplying the scan cost.

Related topics

SQL queries and joins · ER model and normalisation · Query processing and optimisation · Transactions, ACID and indexing · NoSQL databases · Database design case studies