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 |
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
- Confusing
GROUP BYwith window functions. - Omitting
ORDER BYwhen calculating sequential metrics. - Using
ROW_NUMBER()without a deterministic tie-breaking rule. - Ignoring null values returned by
LAG()orLEAD(). - Assuming a row-based moving average always represents a fixed time interval.
- Writing deeply nested queries when CTEs would improve readability.
Data Science Tutorial for Beginners
Vaishali Ardhana








