How to Use Window Functions for Advanced Analytics in SQL: A Complete Guide

Window functions enable calculations across sets of related rows without collapsing the result set, using PARTITION BY for grouping, ORDER BY for sequencing, and ROWS BETWEEN clauses to define precise calculation boundaries for cumulative and rolling metrics.

Window functions are fundamental to modern data engineering pipelines, allowing you to perform sophisticated analytics like running totals, rolling averages, and cohort retention analysis while maintaining row-level granularity. The DataExpert-io/data-engineer-handbook repository contains production-ready SQL patterns in the intermediate bootcamp materials that demonstrate exactly how to implement these advanced analytics techniques. This guide explains the core syntax and practical applications found in window_based_analysis.sql and related files.

Core Syntax for SQL Window Functions

Window functions operate on a "window" of rows related to the current row, defined by three essential components that determine how calculations are scoped and executed.

Partitioning with PARTITION BY

The PARTITION BY clause groups rows into independent segments, ensuring calculations reset for each distinct group. In window_based_analysis.sql, queries partition by combinations like referrer and url to isolate traffic patterns for specific sources independently.

SUM(COUNT(*)) OVER (
    PARTITION BY referrer, url
    ORDER BY event_date
) AS partitioned_metric

Ordering with ORDER BY

The ORDER BY clause within the OVER() expression defines the sequence of rows inside each partition. This ordering is mandatory for cumulative calculations and determines the direction of running totals—whether summing from the beginning of time or moving backward from the current row.

Frame Specification with ROWS BETWEEN

The frame clause defines exactly which rows participate in the calculation relative to the current row. According to the Data Engineer Handbook source code, common frame specifications include:

• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING – Includes every row in the partition, producing a total cumulative sum across the entire series.
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW – Includes all previous rows plus the current row, creating a running total up to the current date.
• ROWS BETWEEN 6 PRECEDING AND CURRENT ROW – Captures the current row plus six previous rows, ideal for 7-day rolling calculations.
• ROWS BETWEEN 13 PRECEDING AND 6 PRECEDING – Isolates the prior week's data by looking back 13 to 6 rows before the current row.

Implementing Advanced Analytics Patterns

The query in intermediate-bootcamp/materials/4-applying-analytical-patterns/lecture-lab/window_based_analysis.sql demonstrates a complete analytics pipeline combining multiple window function techniques.

Aggregating Raw Events

First, the query aggregates raw event data into daily counts per referrer and url using standard GROUP BY operations. This creates the foundational dataset upon which window functions operate.

SELECT
    referrer,
    url,
    event_date,
    COUNT(*) AS daily_visits
FROM events
GROUP BY referrer, url, event_date

Multi-Layered Cumulative Calculations

The implementation applies multiple window functions simultaneously to derive different cumulative perspectives:

• monthly_cumulative_sum – Resets monthly using PARTITION BY referrer, url, DATE_TRUNC('month', event_date)
• rolling_cumulative_sum – Running total from the start of the dataset using UNBOUNDED PRECEDING AND CURRENT ROW
• total_cumulative_sum – Fixed total for the entire partition using UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Rolling Window Analysis

For trend analysis, the query calculates weekly_rolling_count using a 7-day frame (6 PRECEDING AND CURRENT ROW) and previous_weekly_rolling_count using the offset frame (13 PRECEDING AND 6 PRECEDING) to compare current week performance against the prior week.

Deriving Percentage Metrics

The query computes the percentage of daily activity against cumulative totals, filtering for rows where total_cumulative_sum exceeds defined thresholds to focus analysis on high-traffic sources.

Real-World SQL Window Function Examples

These patterns from the Data Engineer Handbook repository apply to cohort analysis, funnel progression, retention metrics, and revenue attribution pipelines.

Calculating Running Totals

To calculate a running total of visits per URL over time, combine PARTITION BY with an unbounded preceding frame:

SELECT
    url,
    event_date,
    COUNT(*) AS daily_visits,
    SUM(COUNT(*)) OVER (
        PARTITION BY url
        ORDER BY event_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM events
GROUP BY url, event_date
ORDER BY url, event_date;

Computing 7-Day Rolling Averages

For smoothed trend analysis of referrer performance, use a bounded frame to calculate the average over the current and previous six days:

SELECT
    referrer,
    url,
    event_date,
    COUNT(*) AS daily_visits,
    AVG(COUNT(*)) OVER (
        PARTITION BY referrer, url
        ORDER BY event_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS weekly_avg
FROM events
GROUP BY referrer, url, event_date
ORDER BY referrer, url, event_date;

Building Retention Cohorts

The retention_analysis.sql file demonstrates tracking user return behavior using window functions to identify users who came back within specific time windows:

WITH first_events AS (
    SELECT
        user_id,
        MIN(event_date) AS cohort_day
    FROM events
    GROUP BY user_id
)
SELECT
    cohort_day,
    SUM(CASE WHEN DATE_DIFF('day', cohort_day, event_date) BETWEEN 1 AND 3 THEN 1 END) 
        OVER (PARTITION BY cohort_day) AS returning_users_3d
FROM events e
JOIN first_events fe ON e.user_id = fe.user_id
WHERE e.event_date > fe.cohort_day;

Related Analytics Files in the Repository

The Data Engineer Handbook includes several complementary files demonstrating window function applications:

• growth_accounting.sql – Implements window functions to track user growth metrics and calculate period-over-period growth rates.
• funnel_analysis.sql – Builds conversion funnels using cumulative counts and LAG()/LEAD() functions to compare step progression.
• grouping_sets.sql – Demonstrates advanced grouping techniques that combine with window analysis for multi-dimensional reporting.

Summary

• Window functions preserve row granularity while calculating aggregates across related rows using OVER() clauses.
• PARTITION BY isolates calculations into independent groups, essential for analyzing segmented data like traffic sources or user cohorts.
• Frame specifications using ROWS BETWEEN control whether you calculate running totals, rolling averages, or fixed-period comparisons.
• The window_based_analysis.sql file in the DataExpert-io/data-engineer-handbook repository provides a complete reference implementation combining monthly cumulative sums, running totals, and weekly rolling counts.
• These patterns extend to retention analysis, growth accounting, and funnel analytics by adjusting partition keys and frame boundaries.

Frequently Asked Questions

What is the difference between window functions and GROUP BY in SQL?

Window functions calculate results across rows while maintaining individual row details, whereas GROUP BY collapses rows into summary aggregates. When you use GROUP BY, you lose the original row granularity and can only return grouping columns and aggregated values. Window functions applied via the OVER() clause allow you to calculate running totals or rankings while keeping all original columns visible, making them essential for time-series analysis and cohort tracking.

How do I calculate a 7-day rolling average using window functions?

Use the ROWS BETWEEN clause with a bounded frame of 6 PRECEDING AND CURRENT ROW. This syntax includes the current row plus the six preceding rows, creating a seven-day window. According to the Data Engineer Handbook implementation, you must also include PARTITION BY to ensure the rolling calculation resets for each distinct group (like referrer or URL) and ORDER BY to ensure chronological sequence within the window.

When should I use UNBOUNDED PRECEDING versus specific row ranges?

Use UNBOUNDED PRECEDING when you need cumulative totals from the start of the partition, and use specific ranges like 6 PRECEDING for fixed-period rolling calculations. UNBOUNDED PRECEDING AND CURRENT ROW generates running totals that grow indefinitely, while 6 PRECEDING AND CURRENT ROW creates a sliding window of exactly seven rows. For comparing current performance to previous periods, use offset frames like 13 PRECEDING AND 6 PRECEDING to isolate historical data without including current values.

Can window functions handle retention analysis and cohort calculations?

Yes, window functions are ideal for retention analysis when combined with Common Table Expressions (CTEs) to establish cohort baselines. As shown in retention_analysis.sql, you first identify each user's first event date (cohort day) using MIN() aggregation, then apply window functions partitioned by that cohort date to count returning users within specific date ranges. The LAG() and LEAD() functions also enable comparing consecutive events to determine if users returned within target retention windows.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}