SQL window functions are the most powerful and underused feature in a data scientist’s SQL toolkit. They let you perform calculations across a set of rows related to the current row — without collapsing rows like GROUP BY does. Ranking, running totals, moving averages, cohort analysis, and session detection all become elegant one-query solutions with window functions.
The Anatomy of a Window Function
function_name() OVER (
PARTITION BY column1, column2 -- define groups (optional)
ORDER BY column3 -- order within each group
ROWS BETWEEN 2 PRECEDING -- frame clause (optional)
AND CURRENT ROW
)
PARTITION BY divides rows into groups — like GROUP BY but without collapsing rows. ORDER BY defines the sequence within each partition. The ROWS/RANGE frame clause defines which rows to include in the calculation relative to the current row.
Ranking Functions
-- Employee salaries ranked within each department
SELECT
employee_id,
name,
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_rank,
NTILE(4) OVER (PARTITION BY department ORDER BY salary DESC) AS quartile
FROM employees;
-- ROW_NUMBER: unique sequential (1,2,3,4 — no ties)
-- RANK: gaps on ties (1,2,2,4 — salary tie → both ranked 2, next is 4)
-- DENSE_RANK: no gaps (1,2,2,3 — no skipped ranks)
-- NTILE(4): divides into 4 equal buckets (quartiles)
-- Get only the top earner per department
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY department
ORDER BY salary DESC) AS rn
FROM employees
)
SELECT * FROM ranked WHERE rn = 1;
Aggregate Window Functions
SELECT
sale_date,
region,
revenue,
-- Running total (cumulative sum)
SUM(revenue) OVER (PARTITION BY region
ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS running_total,
-- 7-day moving average
AVG(revenue) OVER (PARTITION BY region
ORDER BY sale_date
ROWS BETWEEN 6 PRECEDING
AND CURRENT ROW) AS moving_avg_7d,
-- Share of total (percentage contribution)
revenue /
SUM(revenue) OVER (PARTITION BY region) * 100 AS pct_of_region,
-- Running max
MAX(revenue) OVER (PARTITION BY region
ORDER BY sale_date) AS running_max,
-- Count of rows so far (cumulative count)
COUNT(*) OVER (PARTITION BY region
ORDER BY sale_date
ROWS UNBOUNDED PRECEDING) AS cumulative_days
FROM daily_sales;
LAG and LEAD – Comparing Rows
SELECT
sale_date,
revenue,
-- Previous day's revenue
LAG(revenue, 1, 0) OVER (ORDER BY sale_date) AS prev_day_revenue,
-- Next day's revenue
LEAD(revenue, 1, 0) OVER (ORDER BY sale_date) AS next_day_revenue,
-- Day-over-day change
revenue - LAG(revenue, 1, 0) OVER (ORDER BY sale_date) AS daily_change,
-- % change vs previous day
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY sale_date))
/ NULLIF(LAG(revenue) OVER (ORDER BY sale_date), 0), 2) AS pct_change,
-- Compare to same day last week (7 rows back)
revenue - LAG(revenue, 7) OVER (ORDER BY sale_date) AS wow_change
FROM daily_sales;
Percentiles and Distribution
SELECT
product_id,
revenue,
-- Exact percentile rank (0 to 1)
PERCENT_RANK() OVER (ORDER BY revenue) AS pct_rank,
-- Cumulative distribution (fraction of values <= current)
CUME_DIST() OVER (ORDER BY revenue) AS cume_dist,
-- Assign to percentile bucket
NTILE(100) OVER (ORDER BY revenue) AS percentile,
-- Is this product in the top 10%?
CASE WHEN PERCENT_RANK() OVER (ORDER BY revenue) >= 0.9
THEN 'Top 10%' ELSE 'Rest' END AS tier
FROM product_sales;
Session Detection
-- Detect user sessions (gap > 30 minutes = new session)
WITH events_with_prev AS (
SELECT
user_id,
event_time,
LAG(event_time) OVER (PARTITION BY user_id
ORDER BY event_time) AS prev_event_time
FROM user_events
),
session_starts AS (
SELECT *,
CASE WHEN prev_event_time IS NULL
OR event_time - prev_event_time > INTERVAL '30 minutes'
THEN 1 ELSE 0 END AS is_new_session
FROM events_with_prev
),
sessions AS (
SELECT *,
SUM(is_new_session) OVER (PARTITION BY user_id
ORDER BY event_time) AS session_id
FROM session_starts
)
SELECT
user_id,
session_id,
MIN(event_time) AS session_start,
MAX(event_time) AS session_end,
COUNT(*) AS events_in_session,
MAX(event_time) - MIN(event_time) AS session_duration
FROM sessions
GROUP BY user_id, session_id
ORDER BY user_id, session_start;
Cohort Retention Analysis
WITH cohorts AS (
-- Assign each user to their signup month cohort
SELECT
user_id,
DATE_TRUNC('month', MIN(order_date)) AS cohort_month
FROM orders
GROUP BY user_id
),
user_activity AS (
SELECT
o.user_id,
c.cohort_month,
DATE_TRUNC('month', o.order_date) AS activity_month,
-- Months since signup
DATEDIFF('month', c.cohort_month,
DATE_TRUNC('month', o.order_date)) AS months_since_signup
FROM orders o
JOIN cohorts c ON o.user_id = c.user_id
)
SELECT
cohort_month,
months_since_signup,
COUNT(DISTINCT user_id) AS active_users,
FIRST_VALUE(COUNT(DISTINCT user_id))
OVER (PARTITION BY cohort_month
ORDER BY months_since_signup) AS cohort_size,
ROUND(100.0 * COUNT(DISTINCT user_id) /
FIRST_VALUE(COUNT(DISTINCT user_id))
OVER (PARTITION BY cohort_month
ORDER BY months_since_signup), 2) AS retention_rate
FROM user_activity
GROUP BY cohort_month, months_since_signup
ORDER BY cohort_month, months_since_signup;
Conclusion
Window functions unlock a class of analytical queries that would otherwise require self-joins, subqueries, or post-processing in Python — making them slower and harder to maintain. Every data scientist who writes SQL should have ROW_NUMBER, LAG/LEAD, running SUM, and moving AVG as automatic tools. Once mastered, window functions reduce complex multi-step analyses to single, readable, high-performance SQL queries that run directly where the data lives.
