-- ROW_NUMBER: Unique sequential number
SELECT
name,
department,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) as overall_rank,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;
-- RANK: Gaps after ties
SELECT
name,
score,
RANK() OVER (ORDER BY score DESC) as rank,
DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank
FROM test_scores;
-- If two people tie for #1, next is #3 with RANK, #2 with DENSE_RANK
-- Running total
SELECT
order_date,
amount,
SUM(amount) OVER (ORDER BY order_date) as running_total
FROM orders;
-- Moving average (last 7 days)
SELECT
date,
revenue,
AVG(revenue) OVER (
ORDER BY date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) as moving_avg_7day
FROM daily_revenue;
-- LAG/LEAD: Access previous/next rows
SELECT
date,
price,
LAG(price) OVER (ORDER BY date) as prev_price,
price - LAG(price) OVER (ORDER BY date) as price_change,
LEAD(price) OVER (ORDER BY date) as next_price
FROM stock_prices;
-- Percentage of total
SELECT
product,
sales,
sales * 100.0 / SUM(sales) OVER () as pct_of_total,
sales * 100.0 / SUM(sales) OVER (PARTITION BY category) as pct_of_category
FROM product_sales;
-- FIRST_VALUE/LAST_VALUE
SELECT
employee_name,
department,
salary,
FIRST_VALUE(employee_name) OVER (
PARTITION BY department
ORDER BY salary DESC
) as highest_paid_in_dept,
LAST_VALUE(employee_name) OVER (
PARTITION BY department
ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) as lowest_paid_in_dept
FROM employees;
-- NTILE: Divide into buckets/quartiles
SELECT
customer_name,
total_purchases,
NTILE(4) OVER (ORDER BY total_purchases DESC) as quartile,
CASE NTILE(4) OVER (ORDER BY total_purchases DESC)
WHEN 1 THEN 'Top 25%'
WHEN 2 THEN 'Upper Middle'
WHEN 3 THEN 'Lower Middle'
WHEN 4 THEN 'Bottom 25%'
END as customer_segment
FROM customer_totals;
-- Cumulative distribution
SELECT
employee_name,
salary,
CUME_DIST() OVER (ORDER BY salary) as cumulative_dist,
PERCENT_RANK() OVER (ORDER BY salary) as percent_rank
FROM employees;
-- Complex frame specification
SELECT
date,
sales,
-- Sum of current row and 2 before/after
SUM(sales) OVER (
ORDER BY date
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
) as smoothed_sales,
-- Average from start of month to current row
AVG(sales) OVER (
PARTITION BY DATE_TRUNC('month', date)
ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) as month_to_date_avg
FROM daily_sales;
-- Find gaps in sequences
SELECT
id,
LAG(id) OVER (ORDER BY id) as prev_id,
id - LAG(id) OVER (ORDER BY id) as gap
FROM orders
WHERE id - LAG(id) OVER (ORDER BY id) > 1;
-- Top N per group
WITH ranked_products AS (
SELECT
category,
product_name,
sales,
ROW_NUMBER() OVER (
PARTITION BY category
ORDER BY sales DESC
) as rank
FROM product_sales
)
SELECT category, product_name, sales
FROM ranked_products
WHERE rank <= 3;
-- Running difference
SELECT
month,
revenue,
revenue - LAG(revenue) OVER (ORDER BY month) as month_over_month_change,
(revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0
/ LAG(revenue) OVER (ORDER BY month) as pct_change
FROM monthly_revenue;
