Advanced · SQL Mastery Series

SQL Advanced: Window Functions, CTEs & Analytics

Window functions and CTEs transform how you think about analytical SQL. Go beyond GROUP BY — compute rankings, running totals, moving averages, period comparisons, and hierarchical data without sacrificing row-level detail.

⏱22 min read📅November 23, 2025✍️Priyanshu Pandey📚SQL Mastery Series

Window Functions — What and Why

Window functions perform calculations across a set of rows (a "window") while still returning one row per result row. This is the key difference from GROUP BY:

GROUP BY collapses rows — you lose row-level detail
Window functions keep every row and add computed values alongside

Real-world use cases:

Rank products by revenue within each category
Compare this month's sales to last month's for each store
Calculate a running total of orders per customer
Find which employees are above average within their department
Identify the top-N items per group

GROUP BY collapses: 1 row per department, row-level detail is GONE

SQL

SELECT department_id, AVG(salary) AS dept_avg
FROM   employees
GROUP  BY department_id;

-- Window function: every employee row is kept + department average added
SELECT emp_id, first_name, salary, department_id,
       AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary
FROM   employees;
-- Result: each employee row PLUS the average for their department alongside

OVER() Syntax

OVER() Syntax — PARTITION BY, ORDER BY & Frames

Every window function uses the OVER() clause to define its window.

SQL

function_name(expression)
OVER (
    PARTITION BY partition_column     -- divide rows into groups
    ORDER BY     order_column         -- define row sequence within each group
    ROWS/RANGE   BETWEEN frame_start  -- define the rows included in each calculation
                 AND     frame_end
)

Components of OVER():

Column	Type	Description
PARTITION BY	Optional	Divides rows into independent groups. Like GROUP BY but without collapsing rows.
ORDER BY	Optional	Defines row sequence inside each partition. Required for ranking and running total functions.
ROWS BETWEEN	Optional frame	Precisely defines the window frame for aggregate window functions.

Frame Specification

Common frame patterns

SQL

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- running total (all rows from start)
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW           -- last 3 rows (moving average over 3)
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING           -- centered 3-row window
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- entire partition

💡

Window functions execute AFTER WHERE, GROUP BY, and HAVING

Window functions are computed last in the SQL execution order — after all filtering. You cannot filter on a window function's result directly in a WHERE clause. Wrap the query in a subquery or CTE and filter on the outer query.

Ranking Functions

Ranking Functions — ROW_NUMBER, RANK, DENSE_RANK, NTILE

Ranking functions assign a position number to each row within a partition.

Compare all four ranking functions side by side — Using a sales table where two products have the same revenue

SQL

SELECT product_name,
       revenue,
       ROW_NUMBER()   OVER (ORDER BY revenue DESC) AS row_num,
       RANK()         OVER (ORDER BY revenue DESC) AS rank,
       DENSE_RANK()   OVER (ORDER BY revenue DESC) AS dense_rank,
       NTILE(4)       OVER (ORDER BY revenue DESC) AS quartile
FROM   product_sales;

-- Example output (with a tie at rank 2):
-- product_name | revenue | row_num | rank | dense_rank | quartile
-- Product A    | 10000   |   1     |  1   |     1      |    1
-- Product B    |  8000   |   2     |  2   |     2      |    1
-- Product C    |  8000   |   3     |  2   |     2      |    2
-- Product D    |  6000   |   4     |  4   |     3      |    2
-- Product E    |  5000   |   5     |  5   |     4      |    3

Column	Type	Description
ROW_NUMBER()	Ranking	Assigns unique numbers — ties broken arbitrarily. No gaps.
RANK()	Ranking	Tied rows get the same rank. Next rank skips (1, 2, 2, 4).
DENSE_RANK()	Ranking	Tied rows get the same rank. No gaps in sequence (1, 2, 2, 3).
NTILE(n)	Ranking	Divides rows into n equal buckets. Returns the bucket number (1 to n).

Top-N Per Group — The Classic Pattern

Top 3 products by revenue in each category

SQL

SELECT *
FROM (
    SELECT category,
           product_name,
           revenue,
           ROW_NUMBER() OVER (
               PARTITION BY category
               ORDER BY     revenue DESC
           ) AS rn
    FROM   product_sales
) ranked
WHERE 3>= rn
ORDER BY category, rn;

✨

ROW_NUMBER vs RANK for top-N

Use ROW_NUMBER() when you want exactly N rows per group (deterministic). Use RANK() <= N when you want to include all tied entries at position N (you may get more than N rows if there are ties at the boundary).

Percentile — Where Does This Row Stand?

What percentile is each employee's salary in their department?

SQL

SELECT emp_id,
       first_name,
       salary,
       department_id,
       ROUND(
           PERCENT_RANK() OVER (
               PARTITION BY department_id
               ORDER BY salary
           ) * 100, 1
       ) AS salary_percentile
FROM employees
ORDER BY department_id, salary;

LAG and LEAD

LAG & LEAD — Comparing Rows Across Periods

LAG looks back at previous rows; LEAD looks ahead at next rows. Both are essential for period-over-period comparisons.

LAG — Access a Previous Row's Value

Monthly sales comparison: current month vs previous month

SQL

SELECT month_year,
       total_sales,
       LAG(total_sales) OVER (ORDER BY month_year) AS prev_month_sales,
       total_sales - LAG(total_sales, 1, 0) OVER (ORDER BY month_year) AS mom_change,
       ROUND(
           (total_sales - LAG(total_sales) OVER (ORDER BY month_year)) 
           / NULLIF(LAG(total_sales) OVER (ORDER BY month_year), 0) * 100, 1
       ) AS mom_pct_change
FROM   monthly_sales
ORDER BY month_year;

-- LAG(value, offset, default)
-- offset: how many rows back (default 1)
-- default: value to return when there is no previous row (default NULL)

LEAD — Access a Next Row's Value

Show each employee's next salary review date

SQL

SELECT emp_id,
       first_name,
       review_date,
       LEAD(review_date) OVER (
           PARTITION BY emp_id
           ORDER BY review_date
       ) AS next_review_date
FROM   performance_reviews
ORDER BY emp_id, review_date;

Store vs. Previous Store Performance

Compare each store's revenue to the prior-year same-month revenue

SQL

SELECT store_id,
       month_year,
       revenue,
       LAG(revenue, 12) OVER (
           PARTITION BY store_id
           ORDER BY month_year
       ) AS same_month_prior_year,
       ROUND(
           (revenue - LAG(revenue, 12) OVER (PARTITION BY store_id ORDER BY month_year))
           / NULLIF(LAG(revenue, 12) OVER (PARTITION BY store_id ORDER BY month_year), 0) * 100, 1
       ) AS yoy_growth_pct
FROM   store_monthly_sales
ORDER BY store_id, month_year;

Running Totals

Running Totals & Moving Averages

Running totals and moving averages are the most common analytical patterns in retail and finance reporting.

Running Total (Cumulative Sum)

Daily running total of sales

SQL

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

-- Running total PER store (PARTITION BY resets the running total per store)
SELECT store_id,
       sale_date,
       daily_revenue,
       SUM(daily_revenue) OVER (
           PARTITION BY store_id
           ORDER BY sale_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS store_cumulative_revenue
FROM   daily_sales
ORDER BY store_id, sale_date;

Moving Average

7-day moving average of sales (current day + 6 preceding days)

SQL

SELECT sale_date,
       daily_revenue,
       ROUND(
           AVG(daily_revenue) OVER (
               ORDER BY sale_date
               ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
           ), 2
       ) AS moving_avg_7day
FROM   daily_sales
ORDER BY sale_date;

-- 30-day moving average for trend analysis
SELECT sale_date,
       daily_revenue,
       ROUND(AVG(daily_revenue) OVER (
           ORDER BY sale_date
           ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
       ), 2) AS ma30
FROM   daily_sales
ORDER BY sale_date;

Running Minimum and Maximum

Track the all-time high daily sales up to each date

SQL

SELECT sale_date,
       daily_revenue,
       MAX(daily_revenue) OVER (
           ORDER BY sale_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS all_time_high,
       MIN(daily_revenue) OVER (
           ORDER BY sale_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS all_time_low
FROM daily_sales
ORDER BY sale_date;

RC:OMS · Multi-Channel Analytics

Window functions in production retail analytics

RC:OMS uses window functions extensively for store performance dashboards, demand forecasting, and cross-channel revenue attribution. The patterns on this page run daily against 100M+ row datasets.

Explore RC:OMS →

CTEs

Common Table Expressions (CTE)

A CTE is a named temporary result set defined using the WITH clause. It exists only for the duration of a single query, but can be referenced multiple times within that query.

Why CTEs over subqueries:

Dramatically more readable — complex queries become step-by-step narratives
Can be referenced multiple times within the same query (unlike inline views)
Make it easy to debug intermediate steps
Required for recursive queries

Basic CTE

Without CTE (nested subqueries — hard to read)

SQL

SELECT department_name, avg_salary
FROM (
    SELECT d.department_name, AVG(e.salary) AS avg_salary
    FROM   employees e JOIN departments d ON d.dept_id = e.department_id
    WHERE  e.status = 'A'
    GROUP  BY d.department_name
) dept_stats
WHERE avg_salary > 60000;

-- With CTE (clear and readable)
WITH dept_stats AS (
    SELECT d.department_name,
           AVG(e.salary) AS avg_salary
    FROM   employees   e
    JOIN   departments d ON d.dept_id = e.department_id
    WHERE  e.status = 'A'
    GROUP  BY d.department_name
)
SELECT department_name, avg_salary
FROM   dept_stats
WHERE  avg_salary > 60000
ORDER  BY avg_salary DESC;

Multiple CTEs — Chained Logic

Multi-step analytics: find top earners by department, then rank departments

SQL

WITH 
dept_avgs AS (
    SELECT department_id,
           AVG(salary) AS dept_avg_salary,
           COUNT(*)    AS headcount
    FROM   employees
    WHERE  status = 'A'
    GROUP  BY department_id
),
dept_ranked AS (
    SELECT da.department_id,
           d.department_name,
           da.dept_avg_salary,
           da.headcount,
           RANK() OVER (ORDER BY da.dept_avg_salary DESC) AS salary_rank
    FROM   dept_avgs da
    JOIN   departments d ON d.dept_id = da.department_id
)
SELECT department_name,
       ROUND(dept_avg_salary, 2) AS avg_salary,
       headcount,
       salary_rank
FROM   dept_ranked
WHERE   5>=salary_rank
ORDER  BY salary_rank;

💡

CTEs are not materialized by default

In most databases (Oracle, PostgreSQL), a CTE is not automatically materialized (cached). The database optimizer may inline the CTE or execute it multiple times if referenced more than once. If you need guaranteed materialization (run once, result cached), use Oracle's WITH /* + MATERIALIZE */ cte_name AS (...) hint, or a global temporary table.

Recursive CTE

Recursive CTE — Hierarchical Queries

Recursive CTEs execute the same query repeatedly, each time building on the results of the previous iteration. They are the standard way to traverse hierarchical or graph-structured data in SQL.

Structure of a Recursive CTE

SQL

WITH RECURSIVE cte_name AS (
    -- Anchor member: the starting point (runs once)
    SELECT ...
    FROM   table
    WHERE  starting_condition

    UNION ALL

    -- Recursive member: references the CTE itself (runs repeatedly)
    SELECT ...
    FROM   table
    JOIN   cte_name ON join_condition  -- links to previous iteration's results
)
SELECT * FROM cte_name;

-- Note: Oracle uses "WITH cte_name AS (...)" — no RECURSIVE keyword required
-- The RECURSIVE keyword is required in PostgreSQL and MySQL 8+

Employee-Manager Hierarchy

Traverse the full management tree from a given root

SQL

WITH emp_hierarchy AS (
    -- Anchor: start with the CEO (manager_id IS NULL)
    SELECT emp_id,
           first_name,
           manager_id,
           0          AS depth,
           first_name AS path
    FROM   employees
    WHERE  manager_id IS NULL

    UNION ALL

    -- Recursive: join each employee to their manager from the previous level
    SELECT e.emp_id,
           e.first_name,
           e.manager_id,
           h.depth + 1,
           h.path || ' > ' || e.first_name  AS path
    FROM   employees     e
    JOIN   emp_hierarchy  h ON h.emp_id = e.manager_id
)
SELECT LPAD(' ', depth * 4) || first_name AS hierarchy_view,
       depth,
       path
FROM   emp_hierarchy
ORDER  BY path;

-- Result:
-- CEO
--     VP Engineering
--         Manager Backend
--             Developer 1
--             Developer 2
--         Manager Frontend

Generating a Number Sequence

Generate numbers 1 to 100 without a calendar or sequence table

SQL

WITH nums AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1
    FROM   nums
    WHERE  n < 100
)
SELECT n FROM nums;

Generating a Date Calendar

Generate every date in Q1 2025

SQL

WITH calendar AS (
    SELECT DATE '2025-01-01' AS cal_date
    UNION ALL
    SELECT cal_date + 1
    FROM   calendar
    WHERE  cal_date < DATE '2025-03-31'
)
SELECT cal_date,
       TO_CHAR(cal_date, 'Day')     AS day_name,
       EXTRACT(WEEK FROM cal_date)  AS week_number
FROM   calendar
ORDER  BY cal_date;

⚠️

Always set a recursion depth limit

A recursive CTE without a proper termination condition will recurse indefinitely and consume all available memory. In Oracle, you can use CYCLE detection. In PostgreSQL, set a maximum iteration count in the WHERE clause (WHERE depth < 20). Always test with small datasets first.

Gotchas

Common Gotchas

Important Gotchas

!
Filtering on window function results in WHERE causes an error. Window functions execute after WHERE. You must wrap the query in a subquery or CTE: WITH ranked AS (SELECT ..., ROW_NUMBER() OVER(...) AS rn FROM ...) SELECT * FROM ranked WHERE rn = 1.
!
RANK() gaps vs DENSE_RANK() no-gaps. When you use RANK() <= 3, you may get more than 3 rows if there are ties at position 3 — and if position 2 has a tie, position 3 becomes position 4 (skip). Decide consciously whether you want RANK or DENSE_RANK for your use case.
!
Missing ORDER BY in window function gives non-deterministic results. Without ORDER BY inside OVER(), functions like ROW_NUMBER() return results in arbitrary order. For running totals and ranking, ORDER BY inside the OVER() clause is mandatory.
!
LAG/LEAD returning NULL on the first/last row. The first row has no previous row, so LAG returns NULL by default. Use the optional third argument LAG(value, 1, 0) to specify a default value instead of NULL.
!
Infinite recursion in recursive CTEs. Always include a termination condition in the recursive member's WHERE clause (e.g., WHERE depth < 50). Circular references in hierarchical data — where employee A reports to B who reports to A — will cause infinite recursion without cycle detection.
!
Confusing ROWS BETWEEN and RANGE BETWEEN. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW looks at the previous 2 physical rows. RANGE BETWEEN 2 PRECEDING AND CURRENT ROW looks at rows where the ORDER BY value is within 2 of the current row — very different when there are ties or gaps in values.

Key Takeaways

Window functions compute across related rows while preserving individual row detail — unlike GROUP BY which collapses rows. The OVER() clause defines the window with PARTITION BY, ORDER BY, and an optional frame.
ROW_NUMBER() assigns unique numbers (no ties). RANK() allows ties with gaps. DENSE_RANK() allows ties without gaps. NTILE(n) divides rows into n equal buckets.
LAG/LEAD access values from previous/next rows without a self-join — essential for period-over-period comparisons, churn detection, and sequence analysis.
Running totals use SUM() OVER (ORDER BY col ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Moving averages use AVG() OVER (ORDER BY col ROWS BETWEEN n PRECEDING AND CURRENT ROW).
CTEs (WITH clause) make complex queries readable and maintainable. Multiple CTEs chain together like steps in a pipeline. CTEs can reference themselves recursively for hierarchical data traversal.
Recursive CTEs need an anchor query (starting point), a recursive query (references the CTE), UNION ALL between them, and a termination condition in the WHERE clause to prevent infinite loops.

← Previous

SQL Intermediate — Joins, Aggregates, Subqueries & More

Next in series →

PL/SQL Complete Guide — Procedural Oracle Programming

SQL Advanced: Window Functions, CTEs & Analytics

Window Functions — What and Why#

OVER() Syntax — PARTITION BY, ORDER BY & Frames#

Frame Specification

Window functions execute AFTER WHERE, GROUP BY, and HAVING

Ranking Functions — ROW_NUMBER, RANK, DENSE_RANK, NTILE#

Top-N Per Group — The Classic Pattern

ROW_NUMBER vs RANK for top-N

Percentile — Where Does This Row Stand?

LAG & LEAD — Comparing Rows Across Periods#

LAG — Access a Previous Row's Value

LEAD — Access a Next Row's Value

Store vs. Previous Store Performance

Running Totals & Moving Averages#

Running Total (Cumulative Sum)

Moving Average

Running Minimum and Maximum

Common Table Expressions (CTE)#

Basic CTE

Multiple CTEs — Chained Logic

CTEs are not materialized by default

Recursive CTE — Hierarchical Queries#

Structure of a Recursive CTE

Employee-Manager Hierarchy

Generating a Number Sequence

Generating a Date Calendar

Always set a recursion depth limit

Common Gotchas#

Important Gotchas

Key Takeaways#

Key Takeaways

Related Articles

SQL Intermediate: Joins, Aggregates, Subqueries, Functions, Views & Indexes

SQL Foundations: The Complete Beginner's Guide

SQL Architecture: CTEs vs. Global Temporary Tables (GTTs)

RC:OMS

RC:Storefront

RC:Pulse

Window Functions — What and Why

OVER() Syntax — PARTITION BY, ORDER BY & Frames

Ranking Functions — ROW_NUMBER, RANK, DENSE_RANK, NTILE

LAG & LEAD — Comparing Rows Across Periods

Running Totals & Moving Averages

Common Table Expressions (CTE)

Recursive CTE — Hierarchical Queries

Common Gotchas

Key Takeaways