📊 SQL Superpowers
Need row numbers? Running totals? Rankings? Window functions perform calculations across table rows without GROUP BY. Game-changing for analytics.
ROW_NUMBER() – Assign Row Numbers
-- ❌ Old way: Can't number rows easily SELECT name, salary, department FROM employees ORDER BY salary DESC; -- ✅ Window function: Add row numbers SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num, name, salary, department FROM employees; -- Result: -- row_num | name | salary | department -- 1 | Alice | 120000 | Engineering -- 2 | Bob | 110000 | Engineering -- 3 | Charlie | 100000 | Sales -- 4 | David | 95000 | Marketing -- Row number per department SELECT ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank, name, salary, department FROM employees; -- Result: Separate numbering per department -- dept_rank | name | salary | department -- 1 | Alice | 120000 | Engineering -- 2 | Bob | 110000 | Engineering -- 1 | Charlie | 100000 | Sales -- 1 | David | 95000 | Marketing
📈 RANK() and DENSE_RANK()
-- RANK() - Gaps in ranking for ties
SELECT
RANK() OVER (ORDER BY score DESC) AS rank,
name,
score
FROM students;
-- score: 100, 100, 95, 90
-- rank: 1, 1, 3, 4 (gap at 2!)
-- DENSE_RANK() - No gaps
SELECT
DENSE_RANK() OVER (ORDER BY score DESC) AS rank,
name,
score
FROM students;
-- score: 100, 100, 95, 90
-- rank: 1, 1, 2, 3 (no gap)
-- Top 3 salaries per department
SELECT *
FROM (
SELECT
name,
salary,
department,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees
) ranked
WHERE dept_rank <= 3;
Running Totals with SUM()
-- Running total of sales
SELECT
order_date,
amount,
SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;
-- Result:
-- order_date | amount | running_total
-- 2024-01-01 | 100 | 100
-- 2024-01-02 | 150 | 250 (100 + 150)
-- 2024-01-03 | 200 | 450 (100 + 150 + 200)
-- Running total per customer
SELECT
customer_id,
order_date,
amount,
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date
) AS customer_running_total
FROM orders;
-- Separate running total for each customer
LAG() and LEAD() - Previous/Next Row
-- Compare with previous day SELECT order_date, revenue, LAG(revenue) OVER (ORDER BY order_date) AS prev_day_revenue, revenue - LAG(revenue) OVER (ORDER BY order_date) AS daily_change FROM daily_revenue; -- Result: -- order_date | revenue | prev_day_revenue | daily_change -- 2024-01-01 | 1000 | NULL | NULL -- 2024-01-02 | 1200 | 1000 | 200 -- 2024-01-03 | 950 | 1200 | -250 -- LEAD() for next row SELECT order_date, revenue, LEAD(revenue) OVER (ORDER BY order_date) AS next_day_revenue FROM daily_revenue; -- LAG with offset (2 days ago) SELECT order_date, revenue, LAG(revenue, 2) OVER (ORDER BY order_date) AS two_days_ago FROM daily_revenue;
🎯 Moving Averages
-- 7-day moving average
SELECT
order_date,
revenue,
AVG(revenue) OVER (
ORDER BY order_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moving_avg_7day
FROM daily_revenue;
-- 3-month moving average
SELECT
month,
total_sales,
AVG(total_sales) OVER (
ORDER BY month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3month
FROM monthly_sales;
NTILE() - Divide into Buckets
-- Divide customers into 4 quartiles by spend SELECT customer_id, total_spend, NTILE(4) OVER (ORDER BY total_spend DESC) AS quartile FROM customer_totals; -- Result: -- customer_id | total_spend | quartile -- 101 | 50000 | 1 (top 25%) -- 102 | 48000 | 1 -- 103 | 30000 | 2 -- 104 | 28000 | 2 -- 105 | 15000 | 3 -- 106 | 12000 | 3 -- 107 | 5000 | 4 (bottom 25%) -- 108 | 3000 | 4 -- Use case: Segment customers SELECT quartile, COUNT(*) as customer_count FROM ( SELECT NTILE(4) OVER (ORDER BY total_spend DESC) AS quartile FROM customer_totals ) quartiles GROUP BY quartile;
✅ Real-World Examples
Pagination
-- Page 2, 10 items per page
SELECT *
FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY created_at DESC) AS row_num,
*
FROM posts
) numbered
WHERE row_num BETWEEN 11 AND 20;
Top N Per Group
-- Top 5 products per category by sales
SELECT category, product_name, total_sales
FROM (
SELECT
category,
product_name,
total_sales,
ROW_NUMBER() OVER (
PARTITION BY category
ORDER BY total_sales DESC
) AS rank
FROM product_sales
) ranked
WHERE rank <= 5;
Percentage of Total
SELECT product, sales, sales * 100.0 / SUM(sales) OVER () AS pct_of_total FROM product_sales; -- Result: -- product | sales | pct_of_total -- A | 5000 | 50.0 -- B | 3000 | 30.0 -- C | 2000 | 20.0
💡 Window Function Syntax
OVER ( [PARTITION BY column] -- Optional: Group by [ORDER BY column] -- Optional: Sort order [ROWS/RANGE clause] -- Optional: Frame specification ) -- Common functions: -- ROW_NUMBER(), RANK(), DENSE_RANK() -- SUM(), AVG(), MIN(), MAX(), COUNT() -- LAG(), LEAD() -- FIRST_VALUE(), LAST_VALUE() -- NTILE()
"Dashboard needed top 10 products per category. Was using subqueries and JOINs. Switched to window functions with ROW_NUMBER(). Query went from 2 seconds to 0.1 seconds. Code so much cleaner."

