SQL Window Functions Cheat Sheet

Covers ROW_NUMBER, RANK, LAG/LEAD, and aggregate window functions with OVER, PARTITION BY, and frame clauses for analytical SQL queries.

2 PagesIntermediateMar 5, 2026

Ranking Functions

ROW_NUMBER, RANK, and DENSE_RANK compared.

sql

SELECT  employee,  department,  salary,  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rank,  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rankFROM employees;-- ROW_NUMBER: always unique, no ties (1,2,3,4)-- RANK: ties share a rank, next rank skips (1,2,2,4)-- DENSE_RANK: ties share a rank, no gap (1,2,2,3)

LAG / LEAD

Access a preceding or following row without a self-join.

sql

SELECT  order_date,  revenue,  LAG(revenue, 1)  OVER (ORDER BY order_date) AS prev_day_revenue,  LEAD(revenue, 1) OVER (ORDER BY order_date) AS next_day_revenue,  revenue - LAG(revenue, 1) OVER (ORDER BY order_date) AS day_over_day_changeFROM daily_sales;-- LAG/LEAD read a value from a preceding/following row without a self-join

Running Totals & Frames

Aggregate window functions with explicit frame boundaries.

sql

SELECT  order_date,  amount,  SUM(amount) OVER (    ORDER BY order_date    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  ) AS running_total,  AVG(amount) OVER (    ORDER BY order_date    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW  ) AS trailing_7day_avg,  amount / SUM(amount) OVER () AS pct_of_totalFROM orders;

Key Concepts

Core building blocks of the window function syntax.

OVER()- Turns an aggregate/ranking function into a window function that returns one row per input row instead of collapsing groups
PARTITION BY- Splits rows into independent groups (like GROUP BY) but each group's rows are still returned individually
ORDER BY (in OVER)- Defines the row order used for ranking, LAG/LEAD, and running calculations within each partition
Frame clause (ROWS/RANGE)- Defines which rows relative to the current row are included, e.g., ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
NTILE(n)- Divides partition rows into n roughly equal buckets, useful for quartiles/deciles
FIRST_VALUE / LAST_VALUE- Returns the first or last value in the current window frame, subject to the frame boundaries

Pro Tip

Window functions run after WHERE, GROUP BY, and HAVING but before ORDER BY and LIMIT — so you can't filter on a window function's result in the same-level WHERE clause; wrap the query in a CTE or subquery and filter in the outer query instead.

Was this cheat sheet helpful?

SQL Joins Deep Dive Cheat Sheet

SQL Query Optimization Cheat Sheet

Explore Topics

#SQLWindowFunctions#SQLWindowFunctionsCheatSheet#Database#Intermediate#RankingFunctions#LAGLEAD#RunningTotalsFrames#KeyConcepts#Functions#Databases#CheatSheet#SkillVeris

Sri Hayavadhana Info-Tech

Professional Web Designing Services