Tips and Tricks #57: Use Window Functions for Running Calculations

Calculate running totals, rankings, and moving averages efficiently with SQL window functions.

Code Snippet

-- Running total of sales by date
SELECT 
    order_date,
    daily_sales,
    SUM(daily_sales) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM daily_sales_summary;

-- 7-day moving average
SELECT 
    order_date,
    daily_sales,
    AVG(daily_sales) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d
FROM daily_sales_summary;

-- Rank customers by total spend
SELECT 
    customer_id,
    total_spend,
    RANK() OVER (ORDER BY total_spend DESC) AS spend_rank,
    NTILE(4) OVER (ORDER BY total_spend DESC) AS quartile
FROM customer_totals;

Why This Helps

• Single query instead of self-joins or subqueries
• 10-100x faster than procedural alternatives
• Cleaner, more maintainable SQL

How to Test

• Verify results against manual calculation
• Check query execution plan

When to Use

Time series analysis, leaderboards, cumulative metrics, cohort analysis.

Performance/Security Notes

Window functions are supported in all major databases. Syntax may vary slightly.

References

• https://mode.com/sql-tutorial/sql-window-functions/

Try this tip in your next project and share your results in the comments!