Window Functions

Aggregate functions like SUM and COUNT collapse multiple rows into one. But what if you want to calculate a running total, rank employees by salary within each department, or compare each row to the next one — all without losing any rows? That's exactly what window functions do. They perform calculations across a set of rows related to the current row, while keeping every row in the result.

The OVER Clause

Every window function uses the OVER() clause to define which rows the function should consider. Without OVER, you get a regular aggregate. With OVER, you get a window function.

-- Create a sales table
CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    salesperson TEXT NOT NULL,
    region TEXT NOT NULL,
    amount REAL,
    sale_date TEXT
);

INSERT INTO sales (id, salesperson, region, amount, sale_date)
VALUES
    (1, 'Alice', 'North', 5000, '2025-01-05'),
    (2, 'Bob', 'South', 4500, '2025-01-08'),
    (3, 'Alice', 'North', 3200, '2025-01-12'),
    (4, 'Carol', 'North', 2800, '2025-01-15'),
    (5, 'Bob', 'South', 6100, '2025-01-18'),
    (6, 'David', 'South', 7200, '2025-01-20'),
    (7, 'Carol', 'North', 3900, '2025-01-22'),
    (8, 'David', 'South', 4800, '2025-01-25');

-- Compare: regular aggregate vs window function
-- Regular aggregate: one row total
SELECT SUM(amount) AS total_sales FROM sales;

-- Window function: every row kept, total shown alongside each
SELECT
    salesperson,
    amount,
    sale_date,
    SUM(amount) OVER () AS total_all_sales,
    ROUND(amount * 100.0 / SUM(amount) OVER (), 1) AS pct_of_total
FROM sales
ORDER BY sale_date;

The OVER () with empty parentheses means "consider all rows." Each row retains its individual data while also showing the grand total.

PARTITION BY

PARTITION BY divides rows into groups (partitions) and applies the window function independently within each group. It's like GROUP BY, but without collapsing rows.

-- Create an employees table
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department TEXT NOT NULL,
    salary INTEGER,
    hire_date TEXT
);

INSERT INTO employees (id, name, department, salary, hire_date)
VALUES
    (1, 'Alice', 'Engineering', 95000, '2020-03-15'),
    (2, 'Bob', 'Engineering', 88000, '2021-06-01'),
    (3, 'Grace', 'Engineering', 102000, '2018-07-14'),
    (4, 'Carol', 'Marketing', 72000, '2019-11-20'),
    (5, 'David', 'Marketing', 68000, '2022-01-10'),
    (6, 'Henry', 'Marketing', 78000, '2021-09-30'),
    (7, 'Eva', 'Sales', 65000, '2023-04-05'),
    (8, 'Frank', 'Sales', 71000, '2020-08-22');

-- Show each employee with their department's stats
SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg,
    salary - ROUND(AVG(salary) OVER (PARTITION BY department), 0) AS diff_from_avg,
    COUNT(*) OVER (PARTITION BY department) AS dept_size
FROM employees
ORDER BY department, salary DESC;

Each partition (department) gets its own independent calculation. Alice's average is computed only across Engineering, Carol's only across Marketing, and so on.

ROW_NUMBER, RANK, and DENSE_RANK

These ranking functions assign a position to each row within a partition.

-- Create a scores table
CREATE TABLE exam_scores (
    id INTEGER PRIMARY KEY,
    student TEXT NOT NULL,
    subject TEXT NOT NULL,
    score INTEGER
);

INSERT INTO exam_scores (id, student, subject, score)
VALUES
    (1, 'Alice', 'Math', 95),
    (2, 'Bob', 'Math', 87),
    (3, 'Carol', 'Math', 95),
    (4, 'David', 'Math', 78),
    (5, 'Eva', 'Math', 87),
    (6, 'Alice', 'Science', 88),
    (7, 'Bob', 'Science', 92),
    (8, 'Carol', 'Science', 85),
    (9, 'David', 'Science', 92),
    (10, 'Eva', 'Science', 79);

-- Compare the three ranking functions
SELECT
    student,
    subject,
    score,
    ROW_NUMBER() OVER (PARTITION BY subject ORDER BY score DESC) AS row_num,
    RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS dense_rank
FROM exam_scores
ORDER BY subject, score DESC;

The differences:

ROW_NUMBER() — Always unique. Ties get arbitrary ordering (1, 2, 3, 4, 5).
RANK() — Ties get the same rank, then skips. (1, 1, 3, 4, 4).
DENSE_RANK() — Ties get the same rank, no gaps. (1, 1, 2, 3, 3).

LAG and LEAD

LAG looks at previous rows; LEAD looks at following rows. These are invaluable for comparing consecutive records.

-- Create a monthly revenue table
CREATE TABLE monthly_revenue (
    id INTEGER PRIMARY KEY,
    month TEXT NOT NULL,
    revenue REAL
);

INSERT INTO monthly_revenue (id, month, revenue)
VALUES
    (1, '2024-07', 42000),
    (2, '2024-08', 45500),
    (3, '2024-09', 38000),
    (4, '2024-10', 51000),
    (5, '2024-11', 48500),
    (6, '2024-12', 62000),
    (7, '2025-01', 55000),
    (8, '2025-02', 58500);

-- Compare each month to the previous and next month
SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_rev,
    LEAD(revenue, 1) OVER (ORDER BY month) AS next_month_rev,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS month_over_month,
    CASE
        WHEN LAG(revenue, 1) OVER (ORDER BY month) IS NULL THEN NULL
        WHEN revenue > LAG(revenue, 1) OVER (ORDER BY month) THEN 'Up'
        WHEN revenue < LAG(revenue, 1) OVER (ORDER BY month) THEN 'Down'
        ELSE 'Flat'
    END AS trend
FROM monthly_revenue
ORDER BY month;

LAG(column, n) looks back n rows (default 1). LEAD(column, n) looks ahead n rows. The first row has no LAG value and the last row has no LEAD value — both return NULL.

Running Totals and Moving Averages

Window functions can compute cumulative values using a frame specification with ROWS BETWEEN.

-- Create a daily sales table
CREATE TABLE daily_sales (
    id INTEGER PRIMARY KEY,
    sale_date TEXT NOT NULL,
    amount REAL
);

INSERT INTO daily_sales (id, sale_date, amount)
VALUES
    (1, '2025-01-01', 1200),
    (2, '2025-01-02', 800),
    (3, '2025-01-03', 1500),
    (4, '2025-01-04', 2100),
    (5, '2025-01-05', 900),
    (6, '2025-01-06', 1800),
    (7, '2025-01-07', 2400),
    (8, '2025-01-08', 1100),
    (9, '2025-01-09', 1600),
    (10, '2025-01-10', 2000);

-- Running total (cumulative sum)
SELECT
    sale_date,
    amount,
    SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM daily_sales
ORDER BY sale_date;

-- 3-day moving average
SELECT
    sale_date,
    amount,
    ROUND(AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 0) AS moving_avg_3day
FROM daily_sales
ORDER BY sale_date;

Frame specifications control which rows the function considers relative to the current row:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — from the start up to now (running total)
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW — the current row and the 2 before it (3-day window)

Practical Example: Sales Leaderboard

Combining multiple window functions to build a comprehensive report.

-- Create a quarterly sales table
CREATE TABLE quarterly_sales (
    id INTEGER PRIMARY KEY,
    salesperson TEXT NOT NULL,
    region TEXT NOT NULL,
    quarter TEXT NOT NULL,
    revenue REAL
);

INSERT INTO quarterly_sales (id, salesperson, region, quarter, revenue)
VALUES
    (1, 'Alice', 'North', 'Q1', 85000),
    (2, 'Alice', 'North', 'Q2', 92000),
    (3, 'Bob', 'North', 'Q1', 78000),
    (4, 'Bob', 'North', 'Q2', 81000),
    (5, 'Carol', 'South', 'Q1', 95000),
    (6, 'Carol', 'South', 'Q2', 88000),
    (7, 'David', 'South', 'Q1', 72000),
    (8, 'David', 'South', 'Q2', 96000);

-- Build a comprehensive leaderboard
SELECT
    salesperson,
    region,
    quarter,
    revenue,
    RANK() OVER (PARTITION BY quarter ORDER BY revenue DESC) AS overall_rank,
    RANK() OVER (PARTITION BY region, quarter ORDER BY revenue DESC) AS region_rank,
    SUM(revenue) OVER (PARTITION BY salesperson ORDER BY quarter) AS cumulative_revenue,
    revenue - LAG(revenue) OVER (PARTITION BY salesperson ORDER BY quarter) AS quarter_change,
    ROUND(revenue * 100.0 / SUM(revenue) OVER (PARTITION BY quarter), 1) AS pct_of_quarter
FROM quarterly_sales
ORDER BY quarter, overall_rank;

Practice Exercises

Try these on your own:

Rank products by price within each category using DENSE_RANK
Calculate month-over-month revenue growth as a percentage using LAG
Create a running total of order amounts for each customer
Find the top 2 highest-paid employees in each department using ROW_NUMBER
Compute a 5-day moving average of daily sales

Key Takeaways

Window functions perform calculations across related rows without collapsing them
OVER() defines the window; PARTITION BY divides it into groups
ROW_NUMBER, RANK, and DENSE_RANK assign positions within partitions
LAG looks at previous rows; LEAD looks at subsequent rows
Frame specifications (ROWS BETWEEN) control running totals and moving averages
Window functions are ideal for rankings, comparisons, and cumulative calculations

Pro Tip: When you need the same window definition multiple times, SQLite supports the WINDOW clause to define it once: WINDOW w AS (PARTITION BY department ORDER BY salary DESC) and then use OVER w in each function. This reduces repetition and makes your query cleaner.

Next Steps

Window functions round out your querying skills. But writing great queries is only half the picture — the other half is having well-structured tables to query against. The next lesson covers database design: normalization, entity-relationship modeling, and the art of choosing primary keys, foreign keys, and constraints. Good design decisions made before the first INSERT prevent entire categories of bugs and performance problems down the road.