# SQL Window Functions: How to Use Them for Advanced Analytics

> Master SQL window functions for advanced analytics. Learn how to perform calculations across row sets without collapsing results using the OVER clause. Enhance your data analysis skills.

- Repository: [DataExpert.io/data-engineer-handbook](https://github.com/DataExpert-io/data-engineer-handbook)
- Tags: deep-dive
- Published: 2026-08-08

---

**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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/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:

```sql
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:

```sql
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:

```sql
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:

```sql
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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/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:

```sql
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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/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.