Prepared by the Uncodemy trainer team• Reviewed by Senior Data Architect• Last reviewed: 24 October 2026
Quick Answer
This page lists Uncodemy's SQL Window Functions PDFs, each as its own file. A window function calculates across a set of related rows while keeping every row in the result. The key syntax is a function followed by OVER with optional PARTITION BY and ORDER BY.
Window frames: ROWS BETWEEN PRECEDING & CURRENT ROW
What is a window function?
A normal aggregate with GROUP BY collapses rows. A window function does not: each row stays, and the function looks at a defined window of related rows. This makes ranking, running totals, comparisons with the previous row and share-of-total calculations possible in one query.
Main families
Window functions in SQL fall into three primary functional families:
Family
Functions
Used for
Ranking
ROW_NUMBER, RANK, DENSE_RANK, NTILE
Top N per group, percentiles
Offset
LAG, LEAD, FIRST_VALUE
Change from previous row, first in group
Aggregate
SUM, AVG, COUNT with OVER
Running totals, moving averages, share of total
Ranking and ties (tested)
Tied salaries show how the three ranking functions differ in SQL execution:
SQL Query: Ranking Functions ComparisonPostgreSQL / MySQL 8+ / SQL Server
SELECT name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
RANK() OVER (ORDER BY salary DESC) AS rk,
DENSE_RANK() OVER (ORDER BY salary DESC) AS drk
FROM employees;
-- ROW_NUMBER never repeats; RANK repeats and skips (1,2,2,4);-- DENSE_RANK repeats without gaps (1,2,2,3).
Running total, previous value and share of total (tested)
Three common analyst patterns combined in a single clean query:
SELECT order_id, order_date, amount,
SUM(amount) OVER (ORDER BY order_date, order_id) AS running_total,
LAG(amount) OVER (ORDER BY order_date, order_id) AS previous_amount,
ROUND(amount * 100.0 / SUM(amount) OVER (), 1) AS pct_of_total
FROM orders;
A moving window with a frame (tested)
A three-row moving sum uses a frame clause to bound the calculation window:
SQL Query: Moving Window with ROWS FrameFrame Specification
SELECT order_date, amount,
SUM(amount) OVER (
ORDER BY order_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_sum_3
FROM orders;
Common mistakes
Forgetting ORDER BY inside OVER: needed for running totals and LAG to order rows correctly.
Using a window function in WHERE: wrap the query in a subquery/CTE and filter outside.
Confusing RANK and DENSE_RANK: understand how ties skip numbers in RANK vs sequential in DENSE_RANK.
Ignoring the frame: default frames (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) can surprise you with ties.
A window function performs a calculation across a set of related rows while keeping each row in the result, using the OVER clause.
Both give tied rows the same rank. RANK skips numbers after ties (1, 2, 2, 4) and DENSE_RANK does not (1, 2, 2, 3).
Use SUM(column) OVER (ORDER BY ordering_column). Add PARTITION BY to restart the total for each group.
LAG returns a value from a previous row and LEAD from a following row, which is useful for period-over-period comparisons.
No. Window functions are evaluated after WHERE. Put the query in a subquery or CTE and filter on the window result outside it.
Yes! All SQL Window Functions PDFs provided on this Uncodemy page are 100% free for students, working professionals, and data analysts for learning and revision.
About these PDFs:
These PDFs are study material for learning and revision. They are not a certificate, a degree or a promise of a job, and technology changes, so check official documentation for the latest details. Spot an error? Write to info@uncodemy.com and the trainer team will review it.
