Menu

Earn Premium with Referrals

Invite your friends and earn Premium rewards through our referral program.

See how it works and start inviting friends.

Window Functions
SQL

Window Functions

Perform advanced analytics like rankings, running totals, and moving averages without collapsing rows.

Window functions allow you to perform calculations across a set of rows that are related to the current row, but unlike aggregate functions, they do not collapse the rows into a single output row.

Key Syntax: OVER()

SELECT column,
       FUNCTION() OVER (PARTITION BY group_col ORDER BY sort_col) AS alias
FROM table;

Components

  • PARTITION BY: Divides the result set into partitions (similar to GROUP BY but without collapsing rows).
  • ORDER BY: Defines the logical order of rows within each partition.
  • ROWS/RANGE: Defines the frame (which rows to include in the calculation).

Types of Window Functions

Ranking Functions

FunctionDescriptionTies Behaviour
ROW_NUMBER()Unique sequential numberEach row gets a unique number (ties broken arbitrarily)
RANK()Same rank for ties, skips numbers1, 2, 2, 4
DENSE_RANK()Same rank for ties, no skip1, 2, 2, 3
NTILE(n)Divides rows into n bucketsEvenly distributes
SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
       RANK() OVER (ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

Value Functions

FunctionDescription
LAG(column, offset)Access the previous row’s value
LEAD(column, offset)Access the next row’s value
FIRST_VALUE(column)First value in the window
LAST_VALUE(column)Last value in the window
SELECT name, salary,
       LAG(salary) OVER (ORDER BY salary) AS prev_salary,
       LEAD(salary) OVER (ORDER BY salary) AS next_salary
FROM employees;

Aggregate Window Functions

Any aggregate function (SUM, AVG, COUNT, MAX, MIN) can be used with OVER():

SELECT sale_date, amount,
       SUM(amount) OVER (ORDER BY sale_date) AS running_total,
       AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg
FROM sales;

Window Frame Specification

The frame defines which rows within the partition are included:

  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — default with ORDER BY.
  • ROWS BETWEEN 6 PRECEDING AND CURRENT ROW — 7-day moving average.
  • RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW — date-based range.

Window Functions vs GROUP BY

GROUP BYWindow Functions
Collapses rows into groupsPreserves individual rows
Returns fewer rowsReturns same number of rows
Cannot access individual row valuesCan access LAG/LEAD

Q: Difference between ROW_NUMBER, RANK, and DENSE_RANK?

A:

  • ROW_NUMBER(): Assigns a unique, sequential integer to rows (1, 2, 3, 4).
  • RANK(): Assigns the same rank to ties, but skips the next ranks (1, 2, 2, 4).
  • DENSE_RANK(): Assigns the same rank to ties and does not skip (1, 2, 2, 3).

Q: What do LAG and LEAD functions do?

A:

  • LAG(): Accesses data from a previous row in the same result set.
  • LEAD(): Accesses data from a subsequent row in the same result set.
  • These are essential for time-series analysis and calculating growth/delta.

Q: Difference between GROUP BY and Window Functions?

A: GROUP BY collapses multiple rows into one summary row. Window functions keep all original rows and append the calculation as a new column for each row.

Q: What does OVER (PARTITION BY) do?

A: It divides the result set into partitions. The window function is applied independently within each partition, similar to how GROUP BY works but without collapsing rows.

1. Calculate running total of sales.

SELECT sale_date, amount,
       SUM(amount) OVER (ORDER BY sale_date) as running_total
FROM sales;

2. Find latest order per customer.

SELECT * FROM (
    SELECT customer_id, order_id, order_date,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) as rn
    FROM orders
) t WHERE rn = 1;

3. Compare salary with previous employee in the same department.

SELECT name, department_id, salary,
       LAG(salary) OVER (PARTITION BY department_id ORDER BY salary) as prev_salary
FROM employees;

4. Find salary difference from department average.

SELECT name, salary,
       salary - AVG(salary) OVER (PARTITION BY department_id) as diff_from_avg
FROM employees;

5. Detect anomalies (7-day moving average).

SELECT date, revenue,
       AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg
FROM daily_stats;

6. Find top 3 salaries per department.

SELECT name, department_id, salary
FROM (
    SELECT name, department_id, salary,
           DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rnk
    FROM employees
) t WHERE rnk <= 3;

7. Calculate month-over-month growth.

SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) as prev_month_revenue,
       ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0 / LAG(revenue) OVER (ORDER BY month), 2) as growth_pct
FROM monthly_revenue;

My Private Notes

Notes are auto-saved locally to this device.