Bits of .NET

📊 Window Functions = Advanced Analytics

Aggregate queries are limiting. Window functions enable running totals, rankings, and moving averages.

📝 Window Function Basics

-- Ranking Functions
SELECT 
    product_name,
    price,
    RANK() OVER (ORDER BY price DESC) as price_rank,
    DENSE_RANK() OVER (ORDER BY price DESC) as dense_rank,
    ROW_NUMBER() OVER (ORDER BY price DESC) as row_num
FROM products;

-- Partitioned Rankings
SELECT 
    category,
    product_name,
    price,
    RANK() OVER (PARTITION BY category ORDER BY price DESC) as category_rank
FROM products;

-- Running Totals
SELECT 
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) as running_total,
    AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg
FROM orders;

-- 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;

🎯 Advanced Window Functions

-- Percentile Calculation
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 for Binning
SELECT 
    customer_id,
    total_spent,
    NTILE(4) OVER (ORDER BY total_spent) as quartile
FROM customer_summary;

-- First and Last Values
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;

-- Moving Average with Custom Window
SELECT 
    date,
    sales,
    AVG(sales) OVER (ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as seven_day_avg,
    AVG(sales) OVER (ORDER BY date ROWS BETWEEN 30 PRECEDING AND CURRENT ROW) as thirty_day_avg
FROM daily_sales;

-- Difference Between Current and Previous
SELECT 
    date,
    sales,
    sales - LAG(sales) OVER (ORDER BY date) as daily_change,
    (sales - LAG(sales) OVER (ORDER BY date)) / LAG(sales) OVER (ORDER BY date) * 100 as percent_change
FROM daily_sales;

💡 Window Function Best Practices

• Use PARTITION BY for grouped operations
• Use ROWS/RANGE for sliding windows
• Combine with CTEs for complex queries
• Index columns used in PARTITION BY
• Test performance with large datasets