SQL Window Functions Cheat Sheet

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)

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

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;

-- ROWS: exactly the 2 preceding physical rows, regardless of value tiesSELECT order_date, amount,  SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rows_sumFROM orders;-- RANGE: includes ALL rows sharing the current ORDER BY value as peers,-- not just a fixed row count -- ties expand the frameSELECT order_date, amount,  SUM(amount) OVER (ORDER BY order_date RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW) AS range_sumFROM orders;-- Default frame when only ORDER BY is given (no ROWS/RANGE specified)-- is 'RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW' -- ties are-- included together, which surprises people expecting a running total-- row by row when the ORDER BY column has duplicate values.

SELECT  department,  employee,  salary,  RANK()   OVER w AS dept_rank,  AVG(salary) OVER w AS dept_avg,  salary - AVG(salary) OVER w AS diff_from_avgFROM employeesWINDOW w AS (PARTITION BY department ORDER BY salary DESC)ORDER BY department, dept_rank;-- Extending a named window with its own frameSELECT order_date, amount,  SUM(amount) OVER w AS running_totalFROM ordersWINDOW w AS (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);

SELECT  student,  score,  PERCENT_RANK() OVER (ORDER BY score) AS pct_rank,   -- (rank-1)/(n-1), range [0,1]  CUME_DIST()    OVER (ORDER BY score) AS cume_dist,  -- fraction of rows <= current  NTILE(4)       OVER (ORDER BY score) AS quartileFROM exam_results;-- Approximate median via PERCENTILE_CONT (ordered-set aggregate, not a-- ranking window function, but often used alongside them)SELECT department,  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salaryFROM employeesGROUP BY department;

-- Trick: subtracting a ROW_NUMBER() from a sequential date collapses-- each consecutive run to a single constant "island" valueWITH numbered AS (  SELECT user_id, login_date,    login_date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date))::int AS grp  FROM daily_logins)SELECT user_id, MIN(login_date) AS streak_start, MAX(login_date) AS streak_end,       COUNT(*) AS streak_lengthFROM numberedGROUP BY user_id, grpORDER BY streak_length DESC;-- Every row in the same unbroken streak gets the same 'grp' value,-- because the gap between login_date and its row number stays constant.