Advanced SQL: Mastering CTEs and Window Functions

SQL developers frequently encounter scenarios that require both complex data aggregation and row-level filtering. Two specific features solve these structural problems: Common Table Expressions (CTEs) and Window Functions.

While powerful independently, combining them enables clean, efficient data transformation. This guide explains the core mechanics of both features and demonstrates the precise technique required to filter data using window functions.

---

1. Common Table Expressions (CTEs)

A Common Table Expression (CTE) creates a temporary, named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs exist only during the execution of the query.

Developers primarily use CTEs to replace deeply nested subqueries. Breaking a massive query into sequential, logical blocks drastically improves readability and maintainability.

Basic Syntax

You define a CTE using the WITH keyword, followed by the expression name and the query definition.

WITH HighValueCustomers AS (
    SELECT 
        customer_id, 
        SUM(total_amount) AS total_spent
    FROM orders
    GROUP BY customer_id
    HAVING SUM(total_amount) > 10000
)
SELECT 
    c.customer_name, 
    hvc.total_spent
FROM customers c
JOIN HighValueCustomers hvc 
  ON c.customer_id = hvc.customer_id;

💡 Developer Tip: Use CTEs for step-by-step debugging. If a massive query returns incorrect data, you can isolate and test each CTE independently by simply changing the final SELECT statement to query a specific CTE block.

---

2. Window Functions

Window functions perform calculations across a defined set of table rows (the "window") related to the current row.

Standard aggregate functions (like SUM or MAX with a GROUP BY clause) collapse multiple rows into a single output row. Window functions operate differently: they compute aggregate values while preserving individual row identities.

Basic Syntax

Window functions require the OVER() clause. Inside OVER(), you typically define the window using PARTITION BY (to group the data) and ORDER BY (to sort the data within the partition).

SELECT 
    employee_id,
    department_id,
    salary,
    RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) as salary_rank
FROM employees;

This query returns every employee, their salary, and their specific salary rank within their department. No rows are collapsed.

💡 Developer Tip: Use the LAG() and LEAD() window functions to compare a row's value directly to the preceding or succeeding row. This eliminates the need for complex, performance-heavy self-joins when calculating month-over-month growth or sequential differences.

---

3. The Core Trick: Combining CTEs and Window Functions

The most frequent roadblock developers face with window functions involves filtering. Standard SQL execution order processes window functions after the WHERE clause. Therefore, attempting to filter directly on a window function yields a syntax error.

This will fail:

-- Syntax Error: Window functions cannot appear in the WHERE clause
SELECT 
    customer_id, 
    order_date,
    ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY order_date DESC) as recent_rank
FROM orders
WHERE recent_rank = 1; 

To solve this execution order conflict, compute the window function inside a CTE. The outer query evaluates after the CTE finishes processing, allowing you to filter on the newly generated column.

The Solution:

WITH RankedOrders AS (
    SELECT 
        customer_id, 
        order_date,
        total_amount,
        ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY order_date DESC) as recent_rank
    FROM orders
)
SELECT 
    customer_id, 
    order_date,
    total_amount
FROM RankedOrders
WHERE recent_rank = 1;

💡 Developer Tip: This pattern—generating a row number in a CTE and filtering for recent_rank = 1 in the main query—is the standard, most reliable method for extracting the "most recent" or "highest value" record per category in relational databases.

---

Summary

CTEs provide structural clarity, and window functions enable complex row-level analytics. Combining them bypasses SQL's execution order limitations. Master this specific pattern to reduce the need for subqueries, temp tables, and heavy application-side data processing.