SQL Window Functions: How to Use Them for Advanced Analytics

SQL window functions perform calculations across sets of rows related to the current row without collapsing the result set, using an OVER (…) clause to define partitions, ordering, and window frames.

SQL window functions enable sophisticated analytical queries that maintain row-level granularity while calculating aggregations across defined subsets of data. According to the DataExpert-io/data-engineer-handbook repository, these functions power essential analytics like running totals, moving averages, and period-over-period comparisons while preserving the detailed rows needed for downstream filtering.

What Are SQL Window Functions?

SQL window functions compute results across a window of rows related to the current row, returning a value for every input row rather than collapsing groups into single summary rows like standard aggregates. This row-preserving behavior makes them indispensable for analytics where you need both individual record details and contextual calculations.

The syntax centers on the OVER (…) clause, which determines exactly which rows participate in each calculation and in what order. Unlike GROUP BY queries that reduce output rows, window functions add computed columns while keeping every original row intact.

Anatomy of the OVER Clause

Three core components define the behavior of SQL window functions within the OVER clause.

PARTITION BY: Logical Grouping

The PARTITION BY clause groups rows into buckets that share common keys, similar to GROUP BY, but each row remains visible in the output. Calculations restart for each partition, enabling independent running totals per category or time period.

ORDER BY: Calculation Sequence

ORDER BY inside the OVER clause establishes the logical sequence for calculations within each partition. This ordering enables cumulative sums, rankings, and lag/lead comparisons based on specific sequences like dates or numeric values.

Window Frames: ROWS BETWEEN

The window frame clause (ROWS BETWEEN …) specifies the exact row range included in each calculation. Common specifications include UNBOUNDED PRECEDING (from the partition start), CURRENT ROW, and specific offsets like 6 PRECEDING for rolling windows.

Real-World SQL Window Function Patterns

The file intermediate-bootcamp/materials/4-applying-analytical-patterns/lecture-lab/window_based_analysis.sql demonstrates five practical patterns for event analytics. These patterns calculate metrics across referrer and URL dimensions while handling temporal logic.

Monthly Cumulative Aggregates

To aggregate counts across an entire month while retaining daily granularity, partition by the month truncated from the event date and specify an unbounded window:

SUM(count) OVER (
    PARTITION BY referrer, url, DATE_TRUNC('month', event_date)
    ORDER BY event_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS monthly_cumulative_sum

This pattern enables percentage-of-month calculations like CAST(count AS REAL) / monthly_cumulative_sum without losing daily row visibility.

Running Totals and Rolling Calculations

For progressive cumulative sums from the start of a partition up to the current row, omit explicit frame boundaries to use the default range:

SUM(count) OVER (
    PARTITION BY referrer, url
    ORDER BY event_date
) AS rolling_cumulative_sum

For 7-day rolling counts that include the current day plus six preceding days, specify exact row offsets:

SUM(count) OVER (
    PARTITION BY referrer, url
    ORDER BY event_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS weekly_rolling_count

Prior-Period Comparisons with Offset Windows

Comparing current metrics to previous periods requires shifting the window frame backward. To capture the week before the current 7-day window, offset the frame boundaries:

SUM(count) OVER (
    PARTITION BY referrer, url
    ORDER BY event_date
    ROWS BETWEEN 13 PRECEDING AND 6 PRECEDING
) AS previous_weekly_rolling_count

This technique powers week-over-week growth analysis by placing two distinct windows side-by-side in the same row.

Complete Implementation Example

The following query from window_based_analysis.sql demonstrates layered window functions applied to web event data. It enriches raw events with device metadata, aggregates to daily granularity, then applies multiple analytical windows:

WITH events_augmented AS (
    SELECT
        COALESCE(d.os_type,      'unknown') AS os_type,
        COALESCE(d.device_type, 'unknown') AS device_type,
        COALESCE(d.browser_type,'unknown') AS browser_type,
        url,
        user_id,
        CASE
            WHEN referrer LIKE '%linkedin%' THEN 'Linkedin'
            WHEN referrer LIKE '%t.co%'      THEN 'Twitter'
            WHEN referrer LIKE '%google%'    THEN 'Google'
            ELSE referrer
        END AS referrer,
        DATE(event_time) AS event_date
    FROM events e
    JOIN devices d ON e.device_id = d.device_id
),

aggregated AS (
    SELECT url, referrer, event_date, COUNT(*) AS count
    FROM events_augmented
    GROUP BY url, referrer, event_date
),

windowed AS (
    SELECT
        referrer,
        url,
        event_date,
        count,

        -- Monthly cumulative sum (all days of the month)
        SUM(count) OVER (
            PARTITION BY referrer, url, DATE_TRUNC('month', event_date)
            ORDER BY event_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS monthly_cumulative_sum,

        -- Rolling cumulative sum (from start of partition up to current row)
        SUM(count) OVER (
            PARTITION BY referrer, url
            ORDER BY event_date
        ) AS rolling_cumulative_sum,

        -- Total cumulative sum (entire partition)
        SUM(count) OVER (
            PARTITION BY referrer, url
            ORDER BY event_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS total_cumulative_sum,

        -- 7-day rolling count (current day + 6 preceding days)
        SUM(count) OVER (
            PARTITION BY referrer, url
            ORDER BY event_date
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) AS weekly_rolling_count,

        -- Prior week rolling count (days 13-7 before current day)
        SUM(count) OVER (
            PARTITION BY referrer, url
            ORDER BY event_date
            ROWS BETWEEN 13 PRECEDING AND 6 PRECEDING
        ) AS previous_weekly_rolling_count
    FROM aggregated
    ORDER BY referrer, url, event_date
)

SELECT
    referrer,
    url,
    event_date,
    count,
    weekly_rolling_count,
    previous_weekly_rolling_count,
    CAST(count AS REAL) / monthly_cumulative_sum AS pct_of_month,
    CAST(count AS REAL) / total_cumulative_sum   AS pct_of_total
FROM windowed
WHERE total_cumulative_sum > 500
  AND referrer IS NOT NULL;

The WHERE total_cumulative_sum > 500 clause demonstrates how window function outputs serve as filters, a capability impossible with standard GROUP BY aggregations alone.

Extending Window Functions to Other Analytics

The patterns in window_based_analysis.sql adapt to other SQL window functions by swapping the aggregate while maintaining the same OVER clause structure. Replace SUM with:

• AVG for moving averages over temperature or stock price data
• ROW_NUMBER for deduplication or pagination within partitions
• RANK or DENSE_RANK for competitive leaderboard positioning
• LAG/LEAD for comparing current rows to previous or subsequent values

The repository's intermediate-bootcamp/materials/4-applying-analytical-patterns/homework/homework.md (Week 4) challenges learners to apply these techniques to baseball statistics, calculating metrics like "most games a team has won in a 90-game stretch." Additionally, grouping_sets.sql demonstrates how window functions complement GROUPING SETS for multi-dimensional reporting.

Summary

• SQL window functions calculate aggregations across related rows while preserving individual row detail, unlike GROUP BY which collapses results.
• The OVER (…) clause defines three critical dimensions: PARTITION BY for grouping, ORDER BY for sequence, and window frames (ROWS BETWEEN) for row selection.
• Unbounded frames (UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) compute totals across entire partitions, while bounded frames (6 PRECEDING) create rolling windows.
• The DataExpert-io/data-engineer-handbook demonstrates these concepts in window_based_analysis.sql, showing layered CTEs that build from raw events to sophisticated cumulative and rolling metrics.
• Window function outputs can appear in WHERE clauses and percentage calculations, enabling complex analytical filters without subqueries.

Frequently Asked Questions

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

GROUP BY collapses rows sharing common keys into single summary rows, eliminating individual record detail. Window functions preserve all rows and add calculated columns based on related rows within defined partitions. You can filter on window function results, whereas filtering on aggregate functions requires a HAVING clause or subquery when using GROUP BY.

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

Use AVG with a ROWS BETWEEN frame that captures the current row plus six preceding rows. The syntax is AVG(value) OVER (PARTITION BY group_column ORDER BY date_column ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). This calculates the mean across exactly seven days of data for each row, restarting the calculation for each partition defined in your PARTITION BY clause.

Can you nest window functions or use them with other SQL features?

Window functions cannot be nested directly (you cannot put one window function inside another), but you can layer them using Common Table Expressions (CTEs) or subqueries. As shown in the DataExpert-io/data-engineer-handbook, window functions combine effectively with CASE statements, JOIN operations, and GROUPING SETS for multi-dimensional analytics, and their outputs can filter results in subsequent query stages.

When should you use ROWS BETWEEN versus RANGE BETWEEN?

Use ROWS BETWEEN when you need a specific count of physical rows (like exactly 7 days of data regardless of date gaps). Use RANGE BETWEEN when you want to include all rows with equal values in the ordering column or handle date intervals logically (like all rows within a 7-day calendar window even if some dates are missing). ROWS offers deterministic performance, while RANGE handles logical value ranges but may have limited support in some database systems.

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