SQL Window Functions Explained: The Complete Visual Guide (2026)

GROUP BY: Collapses rows

SELECT district, COUNT(*) as crime_count
FROM crime_scenes
GROUP BY district;

-- Result: One row per district
-- district    | crime_count
-- Downtown    | 15
-- Waterfront  | 8

Window Function: Keeps all rows

SELECT 
    case_id,
    district,
    COUNT(*) OVER(PARTITION BY district) as district_crime_count
FROM crime_scenes;

-- Result: Every row kept, count added as column
-- case_id | district    | district_crime_count
-- 001     | Downtown    | 15
-- 002     | Downtown    | 15
-- 003     | Waterfront  | 8

Every original row is preserved. The count is calculated for each row's partition.

Running total per district

SELECT 
    crime_date,
    district,
    reported_crimes,
    SUM(reported_crimes) OVER(
        PARTITION BY district 
        ORDER BY crime_date
    ) as running_total
FROM daily_crime_stats;

-- Result:
-- crime_date  | district   | reported_crimes | running_total
-- 2026-01-01  | Downtown   | 5               | 5
-- 2026-01-02  | Downtown   | 3               | 8
-- 2026-01-03  | Downtown   | 7               | 15
-- 2026-01-01  | Waterfront | 2               | 2
-- 2026-01-02  | Waterfront | 4               | 6

The running total resets for each district partition.

All three functions on the same data

SELECT 
    suspect_name,
    prior_convictions,
    ROW_NUMBER() OVER(ORDER BY prior_convictions DESC) as row_num,
    RANK() OVER(ORDER BY prior_convictions DESC) as rank,
    DENSE_RANK() OVER(ORDER BY prior_convictions DESC) as dense_rank
FROM suspects;

-- Result (notice the ties at 5 convictions):
-- suspect_name | prior_convictions | row_num | rank | dense_rank
-- Mike         | 7                 | 1       | 1    | 1
-- Sara         | 5                 | 2       | 2    | 2
-- John         | 5                 | 3       | 2    | 2
-- Lisa         | 3                 | 4       | 4    | 3
-- Tom          | 1                 | 5       | 5    | 4

Sara and John both have 5 convictions. ROW_NUMBER assigns them 2 and 3. RANK gives both 2, then skips to 4. DENSE_RANK gives both 2, then continues to 3.

Running total and percentage of total

SELECT 
    detective,
    cases_solved,
    SUM(cases_solved) OVER(ORDER BY detective) as running_total,
    ROUND(
        100.0 * cases_solved / SUM(cases_solved) OVER(), 
        1
    ) as pct_of_total
FROM case_closures;

-- Result:
-- detective | cases_solved | running_total | pct_of_total
-- John      | 7            | 7             | 46.7
-- Mike      | 5            | 12            | 33.3
-- Sara      | 3            | 15            | 20.0

SUM() OVER() with no ORDER BY calculates the grand total for percentage calculations.

3-day moving average of crimes

SELECT 
    crime_date,
    daily_crimes,
    ROUND(
        AVG(daily_crimes) OVER(
            ORDER BY crime_date 
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ), 
        1
    ) as moving_avg_3day
FROM daily_crime_stats;

-- Result:
-- crime_date  | daily_crimes | moving_avg_3day
-- 2026-01-01  | 5            | 5.0
-- 2026-01-02  | 8            | 6.5
-- 2026-01-03  | 3            | 5.3
-- 2026-01-04  | 6            | 5.7

The frame ROWS BETWEEN 2 PRECEDING AND CURRENT ROW limits the average to the current row and two previous rows.

Month-over-month crime comparison

SELECT 
    month,
    crimes,
    LAG(crimes, 1) OVER(ORDER BY month) as prev_month,
    crimes - LAG(crimes, 1) OVER(ORDER BY month) as change,
    LEAD(crimes, 1) OVER(ORDER BY month) as next_month
FROM monthly_stats;

-- LAG(crimes, 1) means "1 row before"
-- LAG(crimes, 2) would mean "2 rows before"
-- LEAD(crimes, 1) means "1 row ahead"

The optional second parameter is the offset (default 1). The optional third parameter is a default value for NULLs.

Split suspects into risk quartiles

SELECT 
    suspect_name,
    prior_convictions,
    NTILE(4) OVER(ORDER BY prior_convictions DESC) as risk_quartile
FROM suspects;

-- Result:
-- suspect_name | prior_convictions | risk_quartile
-- Mike         | 12                | 1  -- Top 25% (highest risk)
-- Sara         | 9                 | 1
-- John         | 7                 | 2
-- Lisa         | 5                 | 2
-- Tom          | 3                 | 3
-- Jane         | 2                 | 3
-- Bob          | 1                 | 4
-- Alice        | 0                 | 4  -- Bottom 25% (lowest risk)

Quartile 1 contains the top 25% of suspects by prior convictions.

Get earliest and latest crime per district

SELECT 
    case_id,
    district,
    crime_date,
    FIRST_VALUE(crime_date) OVER(
        PARTITION BY district 
        ORDER BY crime_date
    ) as earliest_in_district,
    LAST_VALUE(crime_date) OVER(
        PARTITION BY district 
        ORDER BY crime_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as latest_in_district
FROM crime_reports;

Notice the explicit frame for LAST_VALUE. Without it, you get unexpected results.

7-day moving average

SELECT 
    crime_date,
    daily_crimes,
    ROUND(
        AVG(daily_crimes) OVER(
            ORDER BY crime_date 
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ),
        1
    ) as moving_avg_7day
FROM daily_crime_stats;

-- ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
-- includes the current row + 6 previous rows = 7 days

ROWS BETWEEN counts physical rows. RANGE BETWEEN uses logical value ranges (useful for date gaps).

Classic: Top 3 suspects per district

WITH ranked_suspects AS (
    SELECT 
        suspect_name,
        district,
        risk_score,
        ROW_NUMBER() OVER(
            PARTITION BY district 
            ORDER BY risk_score DESC
        ) as rank_in_district
    FROM suspects
)
SELECT *
FROM ranked_suspects
WHERE rank_in_district <= 3;

-- Returns top 3 highest-risk suspects for each district

This "top N per group" pattern appears in almost every SQL interview.