SQL Advanced Cheatsheet — Window Functions

Window Function Basics

SELECT duration, 
SUM(duration) OVER (ORDER BY start_time) AS running_total
FROM bikeshare_stats
SELECT start_terminal, duration, 
SUM(duration) OVER (PARTITION BY start_terminal
ORDER BY start_time) AS running_total
FROM bikeshare_stats

ROW_NUMBER

SELECT *, ROW_NUMBER() OVER (PARTITION BY department_ID
ORDER BY salary DESC) AS rank_in_dep
FROM employee_salaries

RANK and DENSE_RANK

SELECT start_terminal, duration, 
RANK() OVER (PARTITION BY start_terminal
ORDER BY start_time) AS rank
FROM bikeshare_stats
Rows 4 and 5 have identical start_time

LAG and LEAD

SELECT start_terminal, duration, 
LAG(duration, 1) OVER (PARTITION BY start_terminal
ORDER BY duration) AS lag,
LEAD(duration, 1) OVER (PARTITION BY start_terminal
ORDER BY duration) AS lead,
duration - LAG(duration, 1) OVER (PARTITION BY start_terminal
ORDER BY duration) AS difference
FROM bikeshare_stats

NTILE

SELECT user, COUNT(*) AS total_emails, 
NTILE(100) OVER (ORDER BY COUNT(*) DESC) AS email_percentile
FROM gmail_stats
GROUP BY user

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store