🚀AdvancedWindow Functions
Advanced Window Functions: NTILE, PERCENT_RANK, CUME_DIST
Use statistical window functions for percentiles, distributions, and bucket analysis.
Overview
Beyond ROW_NUMBER and RANK, PostgreSQL offers statistical window functions: NTILE(n) divides rows into n equal buckets; PERCENT_RANK returns the relative rank as a fraction (0 to 1); CUME_DIST returns the cumulative distribution (fraction of rows with value <= current); FIRST_VALUE/LAST_VALUE get the first/last value in the window; NTH_VALUE gets the nth value.
These are essential for percentile analysis, top-N% queries, and statistical reporting on large datasets.
Key Points
- •NTILE(n) divide rows into n equal-sized buckets (1 to n)
- •PERCENT_RANK() (rank-1)/(rows-1), range 0 to 1
- •CUME_DIST() cumulative distribution: fraction of rows <= current
- •FIRST_VALUE(col)/LAST_VALUE(col) first/last in window frame
- •NTH_VALUE(col, n) nth value in window
- •PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col) median
Examples
Statistical window functions
SELECT
name, salary,
NTILE(4) OVER (ORDER BY salary) AS quartile,
PERCENT_RANK() OVER (ORDER BY salary) AS pct_rank,
CUME_DIST() OVER (ORDER BY salary) AS cume_dist,
FIRST_VALUE(name) OVER (ORDER BY salary) AS lowest_earner,
LAST_VALUE(name) OVER (
ORDER BY salary
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS highest_earner
FROM employees;
-- Ordered-set aggregate for median (not window function)
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees;Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4