Window Functions in SQL: Practical Guide & Examples

In technical interviews and real-world analytics, questions requiring comparisons against group averages, period-over-period growth, or deduplicating top records are solved using window functions.

Standardized under the ISO/IEC 9075 SQL Standard and implemented across engines like the PostgreSQL Window Functions Tutorial and PostgreSQL Window Functions Syntax Manual, window functions eliminate slow and convoluted self-joins.

Download Companion Practice Files

Follow along using our companion sales dataset:

1. The Core Problem: Why GROUP BY Isn't Enough

Consider this sample sales dataset:

 sale_id | employee_name | department  | sale_date  | sale_amount
---------+---------------+-------------+------------+-------------
       1 | Aarav         | Enterprise  | 2026-01-10 |       12000
       2 | Diya          | Enterprise  | 2026-01-15 |       18500
       5 | Rohan         | SMB         | 2026-01-08 |        4200
       6 | Priya         | SMB         | 2026-01-20 |        6800

The GROUP BY Collapse

If your manager asks: "What is each employee's sale compared to their department average?", a standard GROUP BY fails:

SELECT department, AVG(sale_amount) AS avg_dept_sale
FROM sales
GROUP BY department;

 department  | avg_dept_sale
-------------+---------------
 Enterprise  |      15250.00
 SMB         |       5500.00

GROUP BY collapses the dataset into two summary rows, destroying individual employee names and sale amounts.

The Window Function Solution

With AVG() OVER (PARTITION BY department), we attach the departmental benchmark directly to every row:

SELECT 
    employee_name,
    department,
    sale_amount,
    ROUND(AVG(sale_amount) OVER (PARTITION BY department), 2) AS dept_avg,
    sale_amount - ROUND(AVG(sale_amount) OVER (PARTITION BY department), 2) AS variance
FROM sales;

 employee_name | department  | sale_amount | dept_avg  | variance 
---------------+-------------+-------------+-----------+----------
 Aarav         | Enterprise  |       12000 |  15250.00 | -3250.00
 Diya          | Enterprise  |       18500 |  15250.00 |  3250.00
 Rohan         | SMB         |        4200 |   5500.00 | -1300.00
 Priya         | SMB         |        6800 |   5500.00 |  1300.00

Every original row is preserved, and the group average is computed dynamically.

2. Anatomy of the OVER() Clause

The OVER() clause instructs the database that a function should be evaluated across a window rather than a collapsed group:

 FUNCTION(col)  OVER ( PARTITION BY dept  ORDER BY sale_date )
 ─────────────         ─────────────────  ──────────────────
   Calculation           Window Slicing     Internal Sequence
   (SUM, RANK)          (Optional Scope)   (Required for order)

1. PARTITION BY (Optional): Divides the rows into logical partitions (e.g. department). The calculation resets at each partition boundary. If omitted, the entire table is treated as one single partition.
2. ORDER BY (Optional / Contextual): Specifies the row order within each partition. Critical for rankings, cumulative running sums, and lead/lag operations.

3. The Three Primary Window Function Families

Family 1: Ranking Functions (ROW_NUMBER, RANK, DENSE_RANK)

These functions assign sequential numbers to rows within a partition based on an order criterion:

SELECT 
    employee_name,
    department,
    sale_amount,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale_amount DESC) AS row_num,
    RANK()       OVER (PARTITION BY department ORDER BY sale_amount DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY sale_amount DESC) AS dense_rnk
FROM sales;

Family 2: Value & Navigation Functions (LAG, LEAD)

LAG() accesses values from a preceding row; LEAD() accesses values from a following row. They calculate period-over-period differences without self-joins:

SELECT 
    employee_name,
    sale_date,
    sale_amount,
    LAG(sale_amount, 1) OVER (PARTITION BY employee_name ORDER BY sale_date) AS prev_sale,
    sale_amount - LAG(sale_amount, 1) OVER (PARTITION BY employee_name ORDER BY sale_date) AS mom_growth
FROM sales;

 employee_name | sale_date  | sale_amount | prev_sale | mom_growth
---------------+------------+-------------+-----------+------------
 Aarav         | 2026-01-10 |       12000 |      NULL |       NULL
 Aarav         | 2026-02-01 |        9500 |     12000 |      -2500
 Diya          | 2026-01-15 |       18500 |      NULL |       NULL
 Diya          | 2026-02-12 |       18500 |     18500 |          0

The first record in each partition evaluates to NULL because there is no prior row to reference.

Family 3: Cumulative Running Aggregates

When combining an aggregate function (SUM, AVG) with an ORDER BY inside OVER(), SQL calculates a cumulative running total from the start of the partition to the current row:

SELECT 
    department,
    sale_date,
    sale_amount,
    SUM(sale_amount) OVER (
        PARTITION BY department 
        ORDER BY sale_date
    ) AS cumulative_dept_sales
FROM sales;

4. The Critical Execution Trap: Filtering with WHERE

In SQL order of execution, WHERE evaluates before SELECT. Because window functions are evaluated during SELECT, writing a window function inside WHERE causes a syntax error:

-- FAILS with error: window functions are not allowed in WHERE
SELECT employee_name, department, sale_amount
FROM sales
WHERE ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale_amount DESC) = 1;

The Solution: Common Table Expression (CTE)

Wrap the window calculation in a CTE or subquery, then filter the resulting alias in the outer WHERE:

WITH ranked_sales AS (
    SELECT 
        employee_name,
        department,
        sale_amount,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale_amount DESC) AS dept_rank
    FROM sales
)
SELECT employee_name, department, sale_amount
FROM ranked_sales
WHERE dept_rank = 1;

For more multi-step query structures, explore our guide on how to use SQL CTEs.

5. Cross-Discipline Comparison: SQL Windows vs Tableau LODs

Analytics professionals moving between business intelligence tools and databases frequently encounter this mental mapping:

Interactive Practice Challenge: Test Your Query

Open sql-window-practice.db in sqliteonline.com and test your understanding with this real-world prompt:

Business Challenge: Write a query that computes each employee's cumulative running sales total within their department, ordered by sale_date.

Expected Output (Sample)

SELECT 
  employee_name,
  department,
  sale_date,
  sale_amount,
  SUM(sale_amount) OVER (
    PARTITION BY department 
    ORDER BY sale_date
  ) AS running_total
FROM sales
ORDER BY department, sale_date;

Summary and Next Steps

Window functions allow you to perform sophisticated analytical transformations—rankings, running totals, and period-over-period deltas—directly in SQL without cumbersome table self-joins.

To continue building your SQL expertise:

• Explore table join mechanics in our SQL joins guide.
• Learn safe null handling in aggregations with our SQL NULL guide.