Menu

SQL Window Functions and CTEs

Basic SQL queries are useful for filtering and summarising data. However, advanced analytical problems often require calculations across multiple stages or comparisons between individual records and larger groups.

SQL provides subqueries, Common Table Expressions (CTEs), and window functions to manage these requirements.

Subqueries place one query inside another, CTEs organise intermediate results into named query blocks, and window functions calculate values across related rows without collapsing them into a single grouped record.

These techniques are particularly useful for ranking customers, calculating running totals, analysing trends, and preparing machine learning features.

What Is a Subquery in SQL?

A subquery is a query nested inside another SQL statement.

Subqueries can be used to calculate reference values or retrieve intermediate results.

Consider an orders table.

order_id customer_id amount
101 C001 12000
102 C002 3500
103 C003 8000
104 C001 2500
105 C004 6000

Suppose the objective is to identify orders greater than the overall average amount.

SELECT
    order_id,
    customer_id,
    amount
FROM orders
WHERE amount > (
    SELECT AVG(amount)
    FROM orders
);

The inner query calculates the average order amount.

\[ Average=\frac{12000+3500+8000+2500+6000}{5} \]

\[ Average=6400 \]

The outer query selects orders worth more than ₹6,400.

Output:

order_id customer_id amount
101 C001 12000
103 C003 8000

What Are Correlated Subqueries?

A correlated subquery refers to values from the outer query.

It is logically evaluated in relation to the outer query's current row, although database engines may optimise its execution.

Consider identifying orders above each customer's average order value.

SELECT
    o1.order_id,
    o1.customer_id,
    o1.amount
FROM orders AS o1
WHERE o1.amount > (
    SELECT AVG(o2.amount)
    FROM orders AS o2
    WHERE o2.customer_id = o1.customer_id
);

The subquery calculates the average order amount for the current customer.

The outer query returns orders exceeding that customer's average.

Correlated subqueries can be expressive, but some patterns may be more efficiently written using joins or window functions.

What Is a Common Table Expression?

A Common Table Expression (CTE) defines a named result that can be referenced within a SQL statement.

CTEs begin with the WITH keyword.

They improve readability when a query involves multiple processing stages.

CTE Example

Consider calculating customer spending and identifying customers who spent more than ₹10,000.

WITH customer_spending AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spending
    FROM orders
    GROUP BY customer_id
)
SELECT
    customer_id,
    total_spending
FROM customer_spending
WHERE total_spending > 10000;

The CTE first calculates total spending by customer.

The outer query filters the resulting customer-level records.

CTEs are particularly useful when analytical calculations require several logical steps.

What Are Window Functions in SQL?

Window functions perform calculations across related rows while preserving individual records.

A window is defined using the OVER() clause.

Unlike GROUP BY, a window function does not automatically collapse multiple records into a single row per group.

For example, a data scientist may calculate the average order amount for each customer while retaining every individual order.

SELECT
    order_id,
    customer_id,
    amount,
    AVG(amount) OVER (
        PARTITION BY customer_id
    ) AS customer_average
FROM orders;

The query returns each order alongside the corresponding customer's average order amount.

What Is PARTITION BY?

PARTITION BY divides rows into groups for window calculations.

For example:

SUM(amount) OVER (
    PARTITION BY customer_id
)

This calculates total spending separately for each customer while keeping the original order rows.

Without PARTITION BY, the window can operate across the complete result set.

The actual frame and ordering also matter for some window functions.

What Is ROW_NUMBER()?

ROW_NUMBER() assigns sequential numbers to rows within a specified window ordering.

Consider ranking each customer's orders from highest to lowest amount.

SELECT
    customer_id,
    order_id,
    amount,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY amount DESC, order_id
    ) AS order_rank
FROM orders;

The function assigns rank 1 to each customer's highest-value order.

Including order_id as a secondary ordering column makes the order deterministic when amounts are tied, assuming order_id is unique.

Finding Each Customer's Highest-Value Order

WITH ranked_orders AS (
    SELECT
        customer_id,
        order_id,
        amount,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY amount DESC, order_id
        ) AS rn
    FROM orders
)
SELECT
    customer_id,
    order_id,
    amount
FROM ranked_orders
WHERE rn = 1;

This returns one highest-value order for each customer.

What Is the Difference Between ROW_NUMBER, RANK, and DENSE_RANK?

These functions assign rankings differently when values are tied.

Consider the scores:

100, 90, 90, 80.

Score ROW_NUMBER RANK DENSE_RANK
100 1 1 1
90 2 2 2
90 3 2 2
80 4 4 3

ROW_NUMBER(): Assigns a unique sequential position.

RANK(): Assigns the same rank to ties and leaves gaps afterward.

DENSE_RANK(): Assigns the same rank to ties without leaving gaps.

For ROW_NUMBER(), the relative ordering of tied values requires an additional sort key if deterministic results are needed.

How Do You Calculate Running Totals?

Running totals calculate cumulative values across ordered observations.

Consider a daily sales table.

sale_date revenue
2026-01-01 5000
2026-01-02 7000
2026-01-03 4000
2026-01-04 8000

A cumulative revenue calculation can be written as:

SELECT
    sale_date,
    revenue,
    SUM(revenue) OVER (
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING
        AND CURRENT ROW
    ) AS cumulative_revenue
FROM daily_sales;

Output:

sale_date revenue cumulative_revenue
2026-01-01 5000 5000
2026-01-02 7000 12000
2026-01-03 4000 16000
2026-01-04 8000 24000

The window frame defines which rows contribute to each cumulative calculation.

The example assumes one row per date.

What Are LAG() and LEAD()?

LAG() retrieves a value from a previous row in the window ordering.

LEAD() retrieves a value from a following row.

These functions are useful for comparing consecutive periods.

Calculating Daily Revenue Change

SELECT
    sale_date,
    revenue,
    LAG(revenue) OVER (
        ORDER BY sale_date
    ) AS previous_revenue,
    revenue - LAG(revenue) OVER (
        ORDER BY sale_date
    ) AS revenue_change
FROM daily_sales;

The first row has no previous observation, so its previous_revenue is NULL.

The calculation helps identify day-to-day changes.

Calculating Percentage Change

WITH revenue_history AS (
    SELECT
        sale_date,
        revenue,
        LAG(revenue) OVER (
            ORDER BY sale_date
        ) AS previous_revenue
    FROM daily_sales
)
SELECT
    sale_date,
    revenue,
    previous_revenue,
    ROUND(
        100.0 * (revenue - previous_revenue)
        / NULLIF(previous_revenue, 0),
        2
    ) AS percentage_change
FROM revenue_history;

NULLIF(previous_revenue, 0) prevents division by zero by returning NULL when the previous value is zero.

What Are Moving Averages in SQL?

A moving average smooths short-term fluctuations by calculating averages over consecutive observations.

For example, a three-observation moving average can be calculated using:

SELECT
    sale_date,
    revenue,
    AVG(revenue) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 2 PRECEDING
        AND CURRENT ROW
    ) AS moving_average
FROM daily_sales;

The window includes the current observation and up to two preceding rows.

For the first two records, fewer than three observations are available.

This is a three-row moving average, not necessarily a three-calendar-day average if dates are missing.

How Are Window Functions Used in Data Science?

Function Analytical Application
ROW_NUMBER() Identify latest records
RANK() Rank products or customers
SUM() OVER() Calculate cumulative metrics
AVG() OVER() Compute group or moving averages
LAG() Compare previous observations
LEAD() Access subsequent observations
NTILE() Divide ordered observations into ranked groups

Window functions are especially useful for time-based feature engineering and behavioural analysis.

For example, a churn model may require the number of days since a customer's previous order or changes in monthly spending.

Common SQL Mistakes

  1. Confusing GROUP BY with window functions.
  2. Omitting ORDER BY when calculating sequential metrics.
  3. Using ROW_NUMBER() without a deterministic tie-breaking rule.
  4. Ignoring null values returned by LAG() or LEAD().
  5. Assuming a row-based moving average always represents a fixed time interval.
  6. Writing deeply nested queries when CTEs would improve readability.

Data Science Tutorial for Beginners

VA

Vaishali Ardhana



Learn via Video Course

undefined Logo

0 hrs



Get in Touch For Details