SQL Window Functions - Essential Analytics Tools for Time-Series Data Analysis

Introduction

Window functions are powerful SQL features that allow you to perform calculations across a set of rows while keeping the individual row details. Think of them as a way to look through a “window” of your data to perform calculations without grouping rows together.

Why Window Functions Matter?

As a data analyst, you often need to answer questions like:

• “What’s our running total of sales over time?”
• “How does each customer’s purchase compare to their previous purchase?”
• “Who are the top 3 customers in each region?”

Window functions make these calculations simple and efficient. Let’s learn through practical examples.

Main Content: Understanding Window Functions

Let’s use this sample data for our examples:

-- Create our sales table
CREATE TABLE daily_sales (
    sale_date DATE,
    product_name VARCHAR(100),
    revenue DECIMAL(10,2),
    region VARCHAR(50)
);

-- Insert sample data
INSERT INTO daily_sales VALUES
('2024-01-01', 'Laptop', 1200.00, 'North'),
('2024-01-01', 'Mouse',    25.00, 'South'),
('2024-01-02', 'Laptop', 1500.00, 'North'),
('2024-01-02', 'Keyboard', 50.00, 'South'),
('2024-01-03', 'Monitor', 300.00, 'North'),
('2024-01-03', 'Laptop', 1200.00, 'South');

Example 1: Running Totals with OVER Clause

Let’s calculate the running total of revenue by region:

SELECT 
    sale_date,
    region,
    revenue,
    SUM(revenue) OVER (
        PARTITION BY region 
        ORDER BY sale_date
    ) as running_total
FROM daily_sales
ORDER BY region, sale_date;

Let’s break down this query:

1. PARTITION BY region:

   • Separates calculations by region
   • Each region gets its own running total
   • Like having separate spreadsheets for each region

2. ORDER BY sale_date:

   • Determines the order for the running total
   • Adds up revenue chronologically

3. SUM(revenue) OVER (...):

   • Calculates running sum while keeping original rows
   • Shows both individual sales and cumulative totals

Results:

sale_date  | region | revenue | running_total
-----------|--------|---------|---------------
2024-01-01 | North  | 1200.00 |       1200.00
2024-01-02 | North  | 1500.00 |       2700.00
2024-01-03 | North  |  300.00 |       3000.00
2024-01-01 | South  |   25.00 |         25.00
2024-01-02 | South  |   50.00 |         75.00
2024-01-03 | South  | 1200.00 |       1275.00

Example 2: Comparing Current to Previous Values

Let’s analyze day-over-day revenue changes using LAG():

SELECT 
    sale_date,
    region,
    revenue as today_revenue,
    LAG(revenue) OVER (
        PARTITION BY region 
        ORDER BY sale_date
    ) as yesterday_revenue,
    -- Calculate percentage change
    ROUND(
        ((revenue - LAG(revenue) OVER (
            PARTITION BY region 
            ORDER BY sale_date)
        ) / LAG(revenue) OVER (
            PARTITION BY region 
            ORDER BY sale_date)
        ) * 100, 
    2) as revenue_change_percent
FROM daily_sales
ORDER BY region, sale_date;

Let’s understand what’s happening:

1. LAG(revenue):

   • Looks back one row to get previous day’s revenue
   • Separate for each region (due to PARTITION BY)
   • Returns NULL for first day (no previous day)

2. Percentage Calculation:

   • (current - previous) / previous * 100
   • Shows percentage increase/decrease
   • Helps identify significant changes

Results:

sale_date  | region | today_revenue | yesterday_revenue | revenue_change_percent
-----------|--------|---------------|------------------|----------------------
2024-01-01 | North  |      1200.00 |            NULL  |                 NULL
2024-01-02 | North  |      1500.00 |         1200.00  |               25.00
2024-01-03 | North  |       300.00 |         1500.00  |              -80.00
2024-01-01 | South  |        25.00 |            NULL  |                 NULL
2024-01-02 | South  |        50.00 |           25.00  |              100.00
2024-01-03 | South  |      1200.00 |           50.00  |             2300.00

Best Practices

1. Clear Window Frame Definitions

   • Specify PARTITION BY when analyzing by categories
   • Always include ORDER BY for running calculations
   • Use appropriate window frame (ROWS/RANGE) for moving averages

2. Performance Considerations

   • Index columns used in PARTITION BY and ORDER BY
   • Avoid unnecessary partitioning
   • Consider materialized views for complex calculations

3. Code Readability

   • Use CTEs to break down complex window functions
   • Add comments explaining business logic
   • Use meaningful column aliases

Remember: Window functions are powerful tools that help you analyze trends and patterns in your data. Start with simple examples and gradually build up to more complex analyses as you become comfortable with the syntax.

Happy analyzing! 🚀