Common Table Expressions (CTEs) and Window Functions

Common Table Expressions (CTEs)

What Are CTEs and When to Use Them

A Common Table Expression (CTE) is a temporary result set that you define within a query using the WITH keyword. CTEs are similar to subqueries but are more readable and reusable. Once defined, a CTE can be referenced within the main query, making it especially useful for organizing complex logic.

Syntax of a CTE:

WITH cte_name AS (
    -- Define the CTE's inner query here
    SELECT column1, column2
    FROM some_table
    WHERE condition
)
-- Main query using the CTE
SELECT *
FROM cte_name
WHERE additional_condition;

When to Use CTEs:

1. Simplifying Complex Queries: CTEs make complex queries easier to read by breaking them into manageable steps.
2. Avoiding Duplicate Code: You can define a complex calculation or filtering logic once in a CTE and reuse it in the main query.
3. Better Organization: CTEs are easier to understand and modify, especially for nested logic or multi-step analysis.

Writing Recursive CTEs for Hierarchical Data

Recursive CTEs allow us to work with hierarchical data, such as organizational charts, family trees, or networked data, by referencing the CTE itself within its definition. Recursive CTEs consist of:

• Anchor Member: The initial query, which retrieves the base or starting rows of the hierarchy.
• Recursive Member: A query that references the CTE itself to retrieve related rows at each level.

Example Use Case: Imagine an employee table where each row has employee_id, name, and manager_id (the ID of the employee’s manager). A recursive CTE could help find each employee’s reporting hierarchy.

Example:

WITH RECURSIVE employee_hierarchy AS (
    -- Anchor Member: Start with employees who don’t have a manager
    SELECT employee_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL
    -- Recursive Member: Find employees reporting to those in the previous level
    SELECT e.employee_id, e.name, e.manager_id, eh.level + 1
    FROM employees AS e
    JOIN employee_hierarchy AS eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

Explanation:

• Anchor Member: Selects employees at the top level (those without a manager).
• Recursive Member: Joins employees with employee_hierarchy to retrieve employees reporting to each previous level.

This CTE will produce a list of employees with their levels in the hierarchy, allowing us to see the chain of command.

Get Tilak Mudgal’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

When to Use Recursive CTEs: Recursive CTEs are ideal for hierarchical or recursive data structures, allowing us to retrieve data across multiple levels in a clear and readable way.

Comparison with Subqueries and Views

CTEs vs. Subqueries:

• Readability: CTEs improve readability by separating different steps and calculations within a query, making it easier to follow than deeply nested subqueries.
• Reusability: CTEs can be referenced multiple times within the same query, which avoids repetition. Subqueries are usually written directly within another query and aren’t as reusable.
• Performance: For complex queries, some databases optimize CTEs better than subqueries, but the performance difference varies depending on the database.

CTEs vs. Views:

• Scope and Lifetime: CTEs exist only for the duration of a single query, while views are stored in the database and can be reused across multiple queries.
• Flexibility: CTEs are defined and used within a single query, making them suitable for temporary or complex logic. Views, however, are better for reusable and standardized logic that multiple users or queries might need.
• Recursive Capabilities: CTEs support recursion, which views typically don’t, making them more suitable for hierarchical data retrieval.

CTEs are a versatile tool in SQL, providing a clear, organized approach to handling complex data analysis and improving both readability and maintainability.

Window Functions

Window functions in SQL are powerful tools for performing calculations across rows related to the current row without collapsing data into groups, unlike GROUP BY. They allow you to compute values such as running totals, ranks, or averages while still being able to access the full dataset in each row. Let’s go through each type with examples.

1. ROW_NUMBER()

Purpose:
ROW_NUMBER assigns a unique number to each row within a partition, based on a specified order. It’s helpful when you want to give each row a unique sequence in a subset of data.

Syntax : ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2)

Example: Assigning unique numbers to bookings within each hotel.

SELECT hotel_id, booking_date, customer_id,
       ROW_NUMBER() OVER (PARTITION BY hotel_id ORDER BY booking_date) AS row_num
FROM bookings;

Why Use It:
ROW_NUMBER is perfect for uniquely identifying rows within each group, useful for pagination, deduplication, or marking sequential events.

2. RANK()

Purpose:
RANK assigns ranks to rows within a partition, creating gaps if there are ties. Rows with equal values get the same rank, and the next rank will skip (e.g., 1, 2, 2, 4).

Syntax: RANK() OVER (PARTITION BY column1 ORDER BY column2)

Example: Ranking customers by total spending within each hotel, with gaps for ties.

SELECT hotel_id, customer_id, total_spent,
       RANK() OVER (PARTITION BY hotel_id ORDER BY total_spent DESC) AS rank
FROM customer_spending;

Why Use It:
RANK is valuable when you need ranks but want to preserve ties with gaps, making it ideal for competitions, ranking products, or prioritizing resources.

3. DENSE_RANK()

Purpose:
Similar to RANK, but DENSE_RANK does not leave gaps between ranks. Rows with the same value receive the same rank, and the next rank continues in sequence.

Syntax: DENSE_RANK() OVER (PARTITION BY column1 ORDER BY column2)

Example: Ranking customers by total spending, without gaps.

SELECT hotel_id, customer_id, total_spent,
       DENSE_RANK() OVER (PARTITION BY hotel_id ORDER BY total_spent DESC) AS dense_rank
FROM customer_spending;

Why Use It:
DENSE_RANK is preferred when you need a continuous rank sequence without gaps, often used in reports or presentations for a cleaner rank structure.

4. SUM(), AVG(), and Other Aggregate Window Functions

Purpose:
Using aggregate functions like SUM, AVG, MIN, MAX, etc., with a window allows you to calculate cumulative sums, moving averages, minimums, and maximums without collapsing rows. These functions provide row-by-row aggregate calculations.

Syntax: SUM(column_name) OVER (PARTITION BY column1 ORDER BY column2)

Example 1: SUM() for Running Total

Query:

SELECT hotel_id, booking_date, total_amount,
       SUM(total_amount) OVER (PARTITION BY hotel_id ORDER BY booking_date) AS running_total
FROM bookings;

Why Use It:
Running totals are useful for cumulative metrics, like tracking cumulative sales or expenses over time, essential in financial and trend analysis.

Example 2: AVG() for Moving Average

Query: Calculate a 2-day moving average for hotel bookings.

SELECT hotel_id, booking_date, total_amount,
       AVG(total_amount) OVER (PARTITION BY hotel_id ORDER BY booking_date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS moving_avg
FROM bookings;

Why Use It:
Moving averages smooth out short-term fluctuations and highlight longer-term trends, which is crucial in time-series analysis, financial forecasting, and inventory management.

5. LAG() and LEAD()

Purpose:
LAG and LEAD allow you to access data from previous or following rows in the dataset, useful for row-by-row comparisons.

• LAG(): Retrieves data from the previous row.
• LEAD(): Retrieves data from the next row.

Syntax:
LAG(column_name, offset, default_value) OVER (PARTITION BY column1 ORDER BY column2)
LEAD(column_name, offset, default_value) OVER (PARTITION BY column1 ORDER BY column2)

Example of LAG(): Compare Current and Previous Booking Amount

Query:

SELECT hotel_id, booking_date, total_amount,
       LAG(total_amount, 1, 0) OVER (PARTITION BY hotel_id ORDER BY booking_date) AS previous_amount
FROM bookings;

Why Use LAG and LEAD:
These functions are essential for comparisons across rows, such as tracking changes over time, monitoring growth, or calculating differences between consecutive values.

Summary

• CTEs: Defined within a single query, ideal for breaking down complex queries, organizing logic, and handling hierarchical data with recursive CTEs.
• Recursive CTEs: Useful for hierarchical data by defining a query that references itself.
• Compared to Subqueries: CTEs are more readable and reusable, improving code organization.
• Compared to Views: CTEs are temporary and flexible, while views are permanent and better for standardized, reusable logic.
• ROW_NUMBER(): Assigns a unique sequential number to each row within a partition.
• RANK(): Assigns ranks with gaps for ties.
• DENSE_RANK(): Assigns ranks without gaps for ties.
• NTILE(n): Divides rows into n groups.
• SUM(), AVG(), etc.: Provides cumulative calculations and moving averages.
• LAG() and LEAD(): Access previous and next row values for comparison.