📊 Window Functions = Data Analytics
Advanced analytics needs window functions. SQL window functions enable complex data analysis.
📝 Window Functions Examples
-- Ranking
SELECT
product_name,
price,
RANK() OVER (ORDER BY price DESC) as rank,
DENSE_RANK() OVER (ORDER BY price DESC) as dense_rank,
ROW_NUMBER() OVER (ORDER BY price DESC) as row_number
FROM products;
-- Partitioned Ranking
SELECT
category,
product_name,
price,
RANK() OVER (PARTITION BY category ORDER BY price DESC) as category_rank
FROM products;
-- Running Total
SELECT
order_date,
amount,
SUM(amount) OVER (ORDER BY order_date) as running_total
FROM orders;
-- Moving Average
SELECT
date,
sales,
AVG(sales) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as seven_day_avg
FROM daily_sales;
🎯 Advanced Analytics
-- LAG and LEAD
SELECT
customer_id,
order_date,
amount,
LAG(amount, 1) OVER (PARTITION BY customer_id ORDER BY order_date) as previous_order,
LEAD(amount, 1) OVER (PARTITION BY customer_id ORDER BY order_date) as next_order
FROM orders;
-- Percentile
SELECT
product_name,
price,
PERCENT_RANK() OVER (ORDER BY price) as percentile_rank,
CUME_DIST() OVER (ORDER BY price) as cumulative_distribution
FROM products;
-- NTILE
SELECT
customer_id,
total_spent,
NTILE(4) OVER (ORDER BY total_spent) as quartile
FROM customer_summary;
-- First and Last Value
SELECT
category,
product_name,
price,
FIRST_VALUE(product_name) OVER (PARTITION BY category ORDER BY price) as cheapest,
LAST_VALUE(product_name) OVER (PARTITION BY category ORDER BY price
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as most_expensive
FROM products;
-- Year over Year Growth
WITH yearly_sales AS (
SELECT
YEAR(order_date) as year,
SUM(amount) as total_sales
FROM orders
GROUP BY YEAR(order_date)
)
SELECT
year,
total_sales,
LAG(total_sales) OVER (ORDER BY year) as previous_year,
(total_sales - LAG(total_sales) OVER (ORDER BY year)) / LAG(total_sales) OVER (ORDER BY year) * 100 as growth
FROM yearly_sales;
💡 Window Function Tips
- Use PARTITION BY for groups
- Use ORDER BY for ordering
- Use ROWS/RANGE for sliding windows
- Combine with CTEs for complex queries
- Index partition columns
Window functions unlock the full power of SQL for analytics. They’re essential for data professionals.
