Part 3: Subqueries, CTEs & Window Functions

1. Subqueries: Nested vs. Correlated

An intermediate developer knows how to write a subquery; a senior engineer knows the architectural performance cost behind it.

Nested (Independent) Subqueries

Executes exactly once from the inside out. The inner query evaluates down to a static literal value or array, and the outer query runs against that unchanging result.

Correlated Subqueries (The Performance Killer)

Executes repeatedly—once for every single row evaluated by the outer query. It relies directly on columns exposed by the outer scope, which can create significant overhead on larger datasets.

High-Yield Scenario: Find employees earning more than the average salary of their specific department

SELECT outer_emp.name, outer_emp.department_id, outer_emp.salary
FROM employees outer_emp
WHERE outer_emp.salary > (
    SELECT AVG(inner_emp.salary)
    FROM employees inner_emp
    WHERE inner_emp.department_id = outer_emp.department_id -- Correlation linkage
);

Existential Checks: EXISTS vs. IN vs. The NOT IN Trap

• EXISTS: Terminates its internal scan the millisecond it finds its first row match. This makes it highly efficient for structural checks.
• IN: Scans the entire subquery array to build an explicit collection for comparison.
• The NOT IN Null Trap: If a subquery returns even a single NULL value, a NOT IN constraint will evaluate to an empty result set for the entire query.

Rule of Thumb: If a column allows null values, always use NOT EXISTS rather than NOT IN to filter out exclusions.

---

2. Common Table Expressions (CTEs) & Recursion

A CTE acts like a readable, named view that exists only for the duration of that specific query execution.

When to deploy a CTE over a Subquery:

• Readability: It breaks up deeply nested logic into a clean, top-to-bottom sequence.
• Reusability: You can reference the same intermediate dataset multiple times within the final query.
• Recursion: It allows you to traverse organizational hierarchies, network graphs, or missing dates.

Recursive CTE Architecture

A recursive CTE joins an Anchor member (the baseline query) to a Recursive member via a UNION or UNION ALL statement, repeating until it hits a termination condition.

Scenario: Generate a series of continuous dates dynamically to check for activity gaps

WITH RECURSIVE date_series AS (
    -- Anchor Member
    SELECT '2026-01-01'::DATE AS report_date
    UNION ALL
    -- Recursive Member
    SELECT (report_date + INTERVAL '1 day')::DATE
    FROM date_series
    WHERE report_date < '2026-01-07' -- Termination condition
)
SELECT report_date FROM date_series;

---

3. The Analytics Engine: Window Functions

Window functions execute multi-row calculations over an explicit partition of your dataset without collapsing the rows into a single summary row, unlike a traditional GROUP BY statement.

The Big Three Ranking Formulas

Function	Tie-Handling Behavior	Example Sequence
ROW_NUMBER()	Assigns strict, sequential integers. Ties are broken arbitrarily.	1, 2, 3, 4
RANK()	Assigns identical values to duplicates. Skips the next rank to balance the counter.	1, 2, 2, 4
DENSE_RANK()	Assigns identical values to duplicates. Does not skip any ranks.	1, 2, 2, 3

Value Functions: LAG() and LEAD()

• LAG(column, offset): Reaches backward into previous rows within the partition to fetch historic snapshots.
• LEAD(column, offset): Reaches forward into upcoming rows to fetch future values.

High-Yield Scenario: Calculate Month-over-Month (MoM) revenue changes

WITH monthly_metrics AS (
    SELECT 
        EXTRACT(MONTH FROM payment_date) AS pay_month,
        SUM(amount) AS current_revenue
    FROM transactions
    GROUP BY EXTRACT(MONTH FROM payment_date)
)
SELECT 
    pay_month,
    current_revenue,
    LAG(current_revenue, 1) OVER (ORDER BY pay_month) AS prior_revenue
FROM monthly_metrics;

---

4. ANY vs ALL

Comparison operators that work against a subquery’s returned set:

• ANY (or SOME) — true if the comparison holds for at least one value in the subquery. WHERE salary > ANY (SELECT salary FROM dept_managers) → salary exceeds at least one manager.
• ALL — true if the comparison holds for every value. WHERE salary > ALL (SELECT salary FROM dept_managers) → higher than every manager (i.e., the highest).

-- ANY: more than the lowest manager
WHERE salary > ANY (SELECT salary FROM managers);
-- ALL: more than the highest manager
WHERE salary > ALL (SELECT salary FROM managers);

Mental model: > ANY behaves like > MIN; > ALL behaves like > MAX. NOT IN ≡ <> ALL, and IN ≡ = ANY.

5. The Nth Highest Salary (Classic Coding Question)

The single most-asked SQL coding question. With ties handled correctly:

-- DENSE_RANK: correct for "Nth distinct salary" (handles ties)
SELECT DISTINCT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) t WHERE rnk = N;

-- LIMIT/OFFSET: fails on ties (skips N rows, not N distinct values)
SELECT DISTINCT salary FROM employees
ORDER BY salary DESC LIMIT 1 OFFSET N-1;

Key point: DENSE_RANK gives no gaps (1,2,2,3) — correct for “Nth distinct salary”; RANK gives gaps (1,2,2,4); ROW_NUMBER is arbitrary under ties. The interviewer wants to see tie-handling.

6. Duplicate Removal Pipeline

How to find and remove duplicate rows:

-- 1. FIND duplicates
SELECT email, COUNT(*) FROM users
GROUP BY email HAVING COUNT(*) > 1;

-- 2. DELETE keeping the lowest id (ROW_NUMBER in a CTE)
WITH ranked AS (
  SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
  FROM users
)
DELETE FROM users WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

-- 3. PREVENT future dupes: add a UNIQUE constraint
ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);

The pattern: find (GROUP BY/HAVING) → dedupe (ROW_NUMBER window, keep rn=1) → prevent (UNIQUE constraint). This is the canonical “write me a query to remove duplicates” answer.