Advanced Window Functions Deep Dive

Advanced SQL

Master the Art of Windowed Analytics

Window functions let you perform calculations across sets of rows related to the current row — without collapsing them with GROUP BY. They are the most powerful analytical tool in SQL and a top interview topic at Google, Amazon, Meta, and Netflix.

Frame Control — Define exactly which rows participate using ROWS, RANGE, and GROUPS clauses
Nested Analytics — Combine multiple window functions for multi-dimensional analysis
Distribution Functions — Compute percentiles, deciles, and cumulative distributions
Real-World Patterns — Running totals, moving averages, gap-and-island detection

Window functions transform row-level data into analytical insights without losing granularity.

What Is a Window Function?

A window function performs a calculation across a window of rows defined by the OVER() clause. Unlike GROUP BY, it preserves all rows while adding computed columns.

Formal Definition

A window function computes a value for each row in a partition using a frame :

where is the ordering defined by ORDER BY.

"Window functions are the SQL equivalent of a moving calculation — they see the past, present, and future of your data." — Database Systems: The Complete Book

Window Function Anatomy

Every window function has four components:

function_name(args) OVER (
    PARTITION BY column    -- 1. Partition: divides rows into groups
    ORDER BY column        -- 2. Order: defines row sequence within partition
    frame_clause           -- 3. Frame: defines the subset of rows to aggregate
    window_name            -- 4. Named window (optional, PostgreSQL/MySQL 8+)
)

The Four Components Visualized

Frame Clause Mastery

The frame clause defines exactly which rows participate in the calculation. There are three frame types:

ROWS vs RANGE vs GROUPS

Frame Type	Semantics	Best For	Performance
`ROWS`	Physical N rows	Moving averages, running totals	Fast (indexed)
`RANGE`	Logical value range	Time-based windows, value thresholds	Moderate
`GROUPS`	Peer groups (ties)	Groups with same ORDER BY value	Slowest

Visual: Frame Types Compared

Given rows with values [10, 20, 20, 30, 40] and ORDER BY value:

ROWS BETWEEN 1 PRECEDING AND CURRENT ROW:

Row 10: [10] → avg = 10
Row 20 (first): [10, 20] → avg = 15
Row 20 (second): [20, 20] → avg = 20
Row 30: [20, 30] → avg = 25

RANGE BETWEEN 10 PRECEDING AND CURRENT ROW:

Row 10: [10] → avg = 10
Row 20 (first): [10, 20, 20] → avg = 16.67
Row 20 (second): [10, 20, 20] → avg = 16.67
Row 30: [10, 20, 20, 30] → avg = 20

GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW:

Row 10: [10] → avg = 10
Row 20 (first): [10, 20, 20] → avg = 16.67
Row 20 (second): [10, 20, 20] → avg = 16.67
Row 30: [20, 20, 30] → avg = 23.33

Mathematical Representation

For a frame with ordering and current row :

ROWS frame:

RANGE frame:

GROUPS frame:

where counts the number of peer groups before .

Frame Clause Examples

-- ROWS: exactly 3 physical rows (current + 2 preceding)
SELECT
  date, revenue,
  AVG(revenue) OVER (
    ORDER BY date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3
FROM daily_sales;

-- RANGE: all rows within 30 calendar days
SELECT
  date, revenue,
  SUM(revenue) OVER (
    ORDER BY date
    RANGE BETWEEN INTERVAL '30' DAY PRECEDING AND CURRENT ROW
  ) AS rolling_30d_sum
FROM daily_sales;

-- GROUPS: current peer group + 1 adjacent group
SELECT
  category, product, sales,
  AVG(sales) OVER (
    ORDER BY category
    GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS avg_adjacent_categories
FROM product_sales;

ℹ️

Pro Tip: The default frame when ORDER BY is present is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This means if you don't specify a frame, the function accumulates from the beginning of the partition to the current row based on value range — which may include more rows than you expect.

Nested Window Functions

Combining multiple window functions in a single query creates powerful multi-dimensional analysis:

-- Multi-dimensional employee analytics
WITH analytics AS (
  SELECT
    employee_id,
    department,
    salary,
    hire_date,

    -- Dimension 1: Rank within department
    DENSE_RANK() OVER (
      PARTITION BY department ORDER BY salary DESC
    ) AS dept_rank,

    -- Dimension 2: Percentile across company
    PERCENT_RANK() OVER (
      ORDER BY salary
    ) AS company_percentile,

    -- Dimension 3: Distance from department average
    salary - AVG(salary) OVER (
      PARTITION BY department
    ) AS diff_from_dept_avg,

    -- Dimension 4: Cumulative distribution within department
    CUME_DIST() OVER (
      PARTITION BY department ORDER BY salary
    ) AS dept_cumulative_dist,

    -- Dimension 5: Running total of department salaries
    SUM(salary) OVER (
      PARTITION BY department
      ORDER BY salary DESC
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS dept_cumulative_salary
  FROM employees
)
SELECT
  employee_id, department, salary,
  dept_rank,
  ROUND(company_percentile * 100, 1) AS percentile,
  diff_from_dept_avg,
  ROUND(dept_cumulative_dist * 100, 1) AS cum_dist_pct,
  dept_cumulative_salary
FROM analytics
WHERE dept_rank <= 5;

Query Execution Flow

FILTER Clause — Conditional Window Aggregation

The FILTER clause (PostgreSQL, SQLite, DuckDB) adds conditional logic inside window functions:

-- Conditional aggregation within window frames
SELECT
  department,
  employee_id,
  salary,

  -- Average salary only for senior employees (>5 years)
  AVG(salary) FILTER (WHERE years_experience > 5)
    OVER (PARTITION BY department) AS avg_senior_salary,

  -- Count of bonuses awarded in department
  COUNT(bonus_amount) FILTER (WHERE bonus_amount > 0)
    OVER (PARTITION BY department) AS bonuses_awarded,

  -- Ratio: current salary vs filtered average
  ROUND(
    salary / NULLIF(
      AVG(salary) FILTER (WHERE employment_status = 'active')
        OVER (PARTITION BY department),
      0
    ) * 100, 1
  ) AS salary_to_active_avg_pct
FROM employees;

ℹ️

Key Insight: Without FILTER, you'd need CASE WHEN inside SUM/COUNT, which is more verbose. The FILTER clause is cleaner and often optimized better by the query planner.

LEAD/LAG with Complex Offsets

Compare with non-adjacent rows using variable offsets and conditional logic:

-- Time-series analysis with LEAD/LAG
SELECT
  store_id,
  sale_date,
  revenue,

  -- Revenue from 7 days ago
  LAG(revenue, 7) OVER (
    PARTITION BY store_id ORDER BY sale_date
  ) AS revenue_7d_ago,

  -- Revenue from same day last year
  LAG(revenue, 1, 0) OVER (
    PARTITION BY store_id, EXTRACT(DOY FROM sale_date)
    ORDER BY sale_date
  ) AS revenue_yoy,

  -- Growth ratio to previous sale
  ROUND(
    revenue / NULLIF(
      LAG(revenue) OVER (
        PARTITION BY store_id ORDER BY sale_date
      ), 0
    ) * 100 - 100, 1
  ) AS growth_pct,

  -- Difference to next sale
  LEAD(revenue, 1, revenue) OVER (
    PARTITION BY store_id ORDER BY sale_date
  ) - revenue AS next_sale_diff,

  -- Flag if revenue increased
  CASE
    WHEN revenue > LAG(revenue) OVER (
      PARTITION BY store_id ORDER BY sale_date
    ) THEN '↑'
    WHEN revenue < LAG(revenue) OVER (
      PARTITION BY store_id ORDER BY sale_date
    ) THEN '↓'
    ELSE '→'
  END AS trend
FROM daily_sales
ORDER BY store_id, sale_date;

LEAD/LAG Mathematical Model

For a sequence ordered by :

Distribution Functions

NTILE, PERCENT_RANK, CUME_DIST, WIDTH_BUCKET

-- Sophisticated data distribution analysis
SELECT
  customer_id,
  total_purchases,
  order_count,

  -- Decile (10 buckets)
  NTILE(10) OVER (ORDER BY total_purchases DESC) AS decile,

  -- Percentile rank
  ROUND(PERCENT_RANK() OVER (ORDER BY total_purchases) * 100, 1) AS percentile,

  -- Cumulative distribution
  ROUND(CUME_DIST() OVER (ORDER BY total_purchases) * 100, 1) AS cum_dist,

  -- Width buckets for histogram
  WIDTH_BUCKET(total_purchases, 0, 10000, 20) AS purchase_bucket,

  -- Continuous percentile (interpolated)
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY total_purchases)
    OVER () AS p95_threshold

FROM customer_stats
WHERE order_count > 1;

Distribution Function Comparison

Function	Range	Ties Handling	Use Case
`NTILE(n)`	1 to n	Even distribution	Bucket assignment
`PERCENT_RANK()`	0 to 1	Average rank	Relative standing
`CUME_DIST()`	0 to 1	Highest rank	Cumulative probability
`WIDTH_BUCKET()`	1 to n+1	Equal-width bins	Histogram buckets

⚠️

Common Mistake: PERCENT_RANK() returns 0 for the lowest value and approaches 1 for the highest. CUME_DIST() returns the fraction of rows ≤ current row. They give different results when there are ties — PERCENT_RANK() averages ties while CUME_DIST() includes all tied rows.

FIRST_VALUE, LAST_VALUE, NTH_VALUE

-- Get specific ordered values from window frames
SELECT
  department,
  employee_id,
  salary,
  hire_date,

  -- Highest paid in department
  FIRST_VALUE(employee_id) OVER (
    PARTITION BY department
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS top_earner_id,

  -- Lowest salary after current row
  LAST_VALUE(salary) OVER (
    PARTITION BY department
    ORDER BY salary ASC
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  ) AS lowest_remaining_salary,

  -- 3rd highest in department
  NTH_VALUE(employee_id, 3) OVER (
    PARTITION BY department
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS third_highest_id

FROM employees;

🚨

Watch Out: LAST_VALUE() is frame-sensitive. Without specifying ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, it defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which gives the same result as FIRST_VALUE() with ascending order. Always specify the full frame for LAST_VALUE().

Window Functions in UPDATE/INSERT

-- Update using window function calculations
UPDATE employee_salary_history
SET salary_band = sub.new_band
FROM (
  SELECT
    employee_id,
    NTILE(5) OVER (ORDER BY salary) AS new_band
  FROM employee_salary_history
  WHERE effective_date = CURRENT_DATE
) sub
WHERE employee_salary_history.employee_id = sub.employee_id
  AND employee_salary_history.effective_date = CURRENT_DATE;

-- PostgreSQL: Named window with WINDOW clause
INSERT INTO department_rankings (department, rank_date, top_salary, avg_salary)
SELECT
  department,
  CURRENT_DATE,
  FIRST_VALUE(salary) OVER w AS top_salary,
  AVG(salary) OVER w AS avg_salary
FROM employees
WINDOW w AS (
  PARTITION BY department
  ORDER BY salary DESC
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
ON CONFLICT (department, rank_date)
DO UPDATE SET
  top_salary = EXCLUDED.top_salary,
  avg_salary = EXCLUDED.avg_salary;

Real-World Example: Employee Salary Analytics

Input Data

employee_id	department	salary	hire_date
1	Engineering	95000	2020-01-15
2	Engineering	82000	2021-03-20
3	Engineering	75000	2022-06-10
4	Marketing	68000	2020-09-01
5	Marketing	72000	2021-11-15
6	Marketing	85000	2019-04-22

Expected Output

employee_id	dept_rank	company_pct	diff_from_avg	cum_dist
1	1	100.0	+17333	1.0
6	1	80.0	+14500	1.0
2	2	60.0	+4333	0.67
5	2	40.0	+3500	0.67
3	3	20.0	-2667	0.33
4	3	0.0	-5500	0.33

Self-Join Optimization Pattern

Avoid expensive self-joins by using window functions for percentile calculations:

-- Efficient percentile calculation (no self-join needed)
WITH percentiles AS (
  SELECT
    department,
    employee_id,
    salary,
    PERCENT_RANK() OVER (
      PARTITION BY department ORDER BY salary
    ) AS pct_rank,
    SUM(salary) OVER (
      PARTITION BY department
      ORDER BY salary
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_salary
  FROM employees
)
SELECT
  department,
  employee_id,
  salary,
  ROUND(pct_rank * 100, 2) AS percentile,
  cumulative_salary,
  ROUND(cumulative_salary / SUM(salary) OVER (PARTITION BY department) * 100, 1) AS pct_of_dept_total
FROM percentiles
WHERE pct_rank BETWEEN 0.9 AND 1.0;

Performance Comparison

Window Function vs Self-Join Performance

Method	Query Time	Rows Processed	Memory
Self-Join	12.4s	25,000,000	2.1 GB
Window Function	3.2s	5,000,000	450 MB
Subquery	8.7s	15,000,000	1.3 GB

Window functions are 3-4x faster than self-joins for analytical queries because they avoid materializing intermediate result sets.

Quiz: Test Your Knowledge

Follow-Up Questions

When would you choose RANGE over ROWS frame, and what are the performance implications?
How does PostgreSQL handle NULLS FIRST/LAST in window function ORDER BY clauses?
Can you use window functions in a WHERE clause? If not, what's the workaround?
What's the difference between DENSE_RANK(), RANK(), and ROW_NUMBER() when there are ties?
How would you compute a 7-day moving average excluding weekends using window functions?
Explain the EXCLUDE clause in window frames (e.g., EXCLUDE CURRENT ROW, EXCLUDE TIES).

Key Takeaways

Frame clauses control which rows participate: ROWS (physical), RANGE (logical), GROUPS (peer sets)
Default frame with ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — always specify explicitly
FILTER clause is cleaner than CASE WHEN for conditional aggregation
LAST_VALUE requires explicit frame specification to avoid common pitfalls
Window functions are 3-4x faster than self-joins for analytical queries
Distribution functions (NTILE, PERCENT_RANK, CUME_DIST) enable sophisticated bucketing

Advanced Window Functions — Frame Clauses, Nested Windows & Analytics