🪟 Window Functions: The PostgreSQL Superpower That Replaces 90% of Your Subqueries

Stop writing nested SELECT nightmares. Window functions are here to save you.

The Problem You Face Daily:

-- Getting each user's rank AND their percentage of total sales
-- Old way: Subquery hell (4.5 seconds)
SELECT 
    user_id,
    sales,
    (SELECT COUNT(*) FROM sales s2 WHERE s2.sales > s1.sales) + 1 as rank,
    ROUND(100.0 * sales / (SELECT SUM(sales) FROM sales), 2) as pct_of_total
FROM sales s1;

The Window Function Magic:

-- New way: Clean and fast (0.2 seconds)
SELECT 
    user_id,
    sales,
    DENSE_RANK() OVER (ORDER BY sales DESC) as rank,
    ROUND(100.0 * sales / SUM(sales) OVER (), 2) as pct_of_total
FROM sales;

22x faster. 75% less code.

🎯 The Big Three You Need:
1. Running Totals & Moving Averages

SELECT 
    date,
    sales,
    SUM(sales) OVER (ORDER BY date) as running_total,
    AVG(sales) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as week_avg
FROM daily_sales;

2. Ranking Within Groups

-- Top 3 products per category
WITH ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) as rn
    FROM products
)
SELECT * FROM ranked WHERE rn <= 3;

3. Previous/Next Row Comparison

SELECT 
    month,
    revenue,
    revenue - LAG(revenue) OVER (ORDER BY month) as growth,
    ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / 
          LAG(revenue) OVER (ORDER BY month), 2) as growth_pct
FROM monthly_revenue;

💡 The Game Changer:

-- Cumulative percentages (impossible without window functions)
SELECT 
    product,
    sales,
    ROUND(100.0 * SUM(sales) OVER (ORDER BY sales DESC) / 
          SUM(sales) OVER (), 2) as cumulative_pct
FROM product_sales
ORDER BY sales DESC;
-- Shows: "Top 20% of products = 80% of sales"

Window functions aren't just faster - they make impossible queries possible.

Tomorrow: CTEs vs Subqueries - which actually performs better?

#PostgreSQL #SQL #WindowFunctions #QueryOptimization

@postgres