What are SQL window functions?
A window function performs a calculation across a set of rows related to the current row — without collapsing them the way GROUP BY does. The window is defined by an OVER clause.
Example — rank employees by salary within each department
SELECT
employee_id,
department,
salary,
RANK() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS salary_rank
FROM employees;💡 This returns every row alongside each employee's salary rank within their department — something you cannot do with GROUP BY alone.
Window functions covered in this track
Ranking functions
ROW_NUMBER()— unique sequential number per rowRANK()— rank with gaps for tiesDENSE_RANK()— rank without gapsNTILE(n)— divide rows into buckets
12 challenges
Practice ranking →Offset functions
LAG(col, n)— access a previous rowLEAD(col, n)— access a future rowFIRST_VALUE(col)— first value in windowLAST_VALUE(col)— last value in window
8 challenges
Practice LAG/LEAD →Aggregate windows
SUM() OVER— running totalsAVG() OVER— moving averagesCOUNT() OVER— running countsMAX() / MIN() OVER— rolling max/min
10 challenges
Practice aggregate windows →Frame clauses
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWROWS BETWEEN N PRECEDING AND N FOLLOWINGRANGEvsROWSframes
6 challenges
Practice frames →