Mastering SQL Window Functions for Complex Time Series Analysis

Time series data is everywhere — from stock prices to website traffic, understanding data over time is critical. SQL window functions provide a powerful way to analyze this data efficiently, even when it appears complex. If you’re new to window functions, this article will guide you through the basics and demonstrate practical examples to analyze time series data.

Window functions operate on a set of rows related to the current row, allowing calculations like running totals, moving averages, and ranking without collapsing your result set. These functions complement GROUP BY by preserving row-level detail while providing aggregated information.

Let's begin by understanding how to use some common window functions with a sample time series table called `sales_data` with the following columns: `sale_date`, `product_id`, and `revenue`.

CREATE TABLE sales_data (
    sale_date DATE,
    product_id INT,
    revenue DECIMAL(10, 2)
);

INSERT INTO sales_data VALUES
('2024-01-01', 1, 100.00),
('2024-01-02', 1, 150.00),
('2024-01-03', 1, 200.00),
('2024-01-01', 2, 80.00),
('2024-01-02', 2, 200.00),
('2024-01-03', 2, 120.00);

### Calculate Running Total by Product A common task is to compute a running total of revenue for each product over time.

SELECT
  sale_date,
  product_id,
  revenue,
  SUM(revenue) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM
  sales_data;

-- This gives a cumulative sum of revenue for each product ordered by date.

### Calculate Moving Average To smooth out short-term fluctuations, you might want a 3-day moving average of revenue per product.

SELECT
  sale_date,
  product_id,
  revenue,
  AVG(revenue) OVER (
    PARTITION BY product_id 
    ORDER BY sale_date 
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3day
FROM
  sales_data;

-- This calculates the average revenue of the current day and two previous days for each product.

### Ranking Days by Revenue Find which days had the highest revenue per product with the RANK() window function.

SELECT
  sale_date,
  product_id,
  revenue,
  RANK() OVER (PARTITION BY product_id ORDER BY revenue DESC) AS revenue_rank
FROM
  sales_data;

-- Days with highest revenue get rank 1, next highest rank 2, etc.

### Difference Between Days Find the day-to-day change in revenue using the LAG() function.

SELECT
  sale_date,
  product_id,
  revenue,
  revenue - LAG(revenue) OVER (PARTITION BY product_id ORDER BY sale_date) AS daily_change
FROM
  sales_data;

-- This shows how much revenue increased or decreased compared to the previous day.

### Summary Mastering window functions lets you analyze time series data in flexible ways without losing detail. Start experimenting with `SUM()`, `AVG()`, `RANK()`, `LAG()`, and `LEAD()` to uncover insights in your data. These functions are widely supported across SQL databases and essential for growing your analytical skills.

Practice these queries on your own data and you'll soon find complex time series analysis simpler and faster with SQL window functions.