SQL Server’s window functions allow you to perform calculations across sets of rows that are related to the current row, without collapsing those rows into a single result like traditional GROUP BY aggregates would. When combined with the DATEDIFF() function, they can open up many options for analyzing temporal patterns in your data.
Moving averages smooth out short-term fluctuations to reveal longer-term trends in your data. Unlike a simple overall average that treats all historical data equally, a moving average focuses on a sliding window of recent events. This can be quite relevant when analyzing process durations, response times, or any time-based metric where you want to understand current performance trends without being overly influenced by distant historical data.
When we calculate moving averages with window functions, we define the frame specification that determines which rows to include. By using ROWS BETWEEN n PRECEDING AND CURRENT ROW, you create a sliding window that moves through your data, recalculating the average at each step based only on the most recent rows.
Example
Imagine you’re monitoring API endpoint response times and want to track not just individual request durations, but also the moving average over the last 5 requests. This helps you identify when performance is degrading over time versus isolated slow requests that might be outliers.
Sample Data
Here’s some sample data for our example:
-- Create the API requests table
CREATE TABLE APIRequests (
RequestID INT PRIMARY KEY IDENTITY(1,1),
EndpointName VARCHAR(100),
RequestTime DATETIME,
ResponseTime DATETIME
);
-- Insert sample data showing varying response times
INSERT INTO APIRequests (EndpointName, RequestTime, ResponseTime)
VALUES
('/api/users', '2025-11-21 10:00:00', '2025-11-21 10:00:00.850'),
('/api/users', '2025-11-21 10:05:00', '2025-11-21 10:05:00.920'),
('/api/users', '2025-11-21 10:10:00', '2025-11-21 10:10:00.780'),
('/api/users', '2025-11-21 10:15:00', '2025-11-21 10:15:01.200'),
('/api/users', '2025-11-21 10:20:00', '2025-11-21 10:20:01.150'),
('/api/users', '2025-11-21 10:25:00', '2025-11-21 10:25:01.500'),
('/api/users', '2025-11-21 10:30:00', '2025-11-21 10:30:01.680'),
('/api/users', '2025-11-21 10:35:00', '2025-11-21 10:35:01.820'),
('/api/users', '2025-11-21 10:40:00', '2025-11-21 10:40:01.750'),
('/api/users', '2025-11-21 10:45:00', '2025-11-21 10:45:01.900'),
('/api/orders', '2025-11-21 10:02:00', '2025-11-21 10:02:01.100'),
('/api/orders', '2025-11-21 10:07:00', '2025-11-21 10:07:01.250'),
('/api/orders', '2025-11-21 10:12:00', '2025-11-21 10:12:00.980'),
('/api/orders', '2025-11-21 10:17:00', '2025-11-21 10:17:01.150'),
('/api/orders', '2025-11-21 10:22:00', '2025-11-21 10:22:01.300'),
('/api/orders', '2025-11-21 10:27:00', '2025-11-21 10:27:01.450'),
('/api/orders', '2025-11-21 10:32:00', '2025-11-21 10:32:01.520'),
('/api/orders', '2025-11-21 10:37:00', '2025-11-21 10:37:01.600');The Query
Now here’s the moving average calculation:
SELECT EndpointName,
RequestTime,
DATEDIFF(millisecond, RequestTime, ResponseTime) AS ResponseTimeMs,
AVG(DATEDIFF(millisecond, RequestTime, ResponseTime))
OVER (PARTITION BY EndpointName
ORDER BY RequestTime
ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS MovingAvg5Requests
FROM APIRequests
ORDER BY EndpointName, RequestTime;Result:
EndpointName RequestTime ResponseTimeMs MovingAvg5Requests
------------ ------------------------ -------------- ------------------
/api/orders 2025-11-21T10:02:00.000Z 1100 1100
/api/orders 2025-11-21T10:07:00.000Z 1250 1175
/api/orders 2025-11-21T10:12:00.000Z 980 1110
/api/orders 2025-11-21T10:17:00.000Z 1150 1120
/api/orders 2025-11-21T10:22:00.000Z 1300 1156
/api/orders 2025-11-21T10:27:00.000Z 1450 1226
/api/orders 2025-11-21T10:32:00.000Z 1520 1280
/api/orders 2025-11-21T10:37:00.000Z 1600 1404
/api/users 2025-11-21T10:00:00.000Z 850 850
/api/users 2025-11-21T10:05:00.000Z 920 885
/api/users 2025-11-21T10:10:00.000Z 780 850
/api/users 2025-11-21T10:15:00.000Z 1200 937
/api/users 2025-11-21T10:20:00.000Z 1150 980
/api/users 2025-11-21T10:25:00.000Z 1500 1110
/api/users 2025-11-21T10:30:00.000Z 1680 1262
/api/users 2025-11-21T10:35:00.000Z 1820 1470
/api/users 2025-11-21T10:40:00.000Z 1750 1580
/api/users 2025-11-21T10:45:00.000Z 1900 1730
This query shows each request’s individual response time alongside the average of the last 5 requests (including the current one).
Take the /api/orders endpoint for example. The first request only averages itself (1100ms), the second averages the first two requests, and by the fifth request you have a true 5-request moving average. As new requests come in, the window slides forward, dropping the oldest request and including the newest one.
Understanding the Moving Window
The frame specification ROWS BETWEEN 4 PRECEDING AND CURRENT ROW creates a window of 5 rows total – the current row plus the 4 rows before it. For the first few rows where there aren’t 4 preceding rows available, SQL Server uses whatever rows exist. This means your first row averages 1 value, the second averages 2 values, and so on until you reach the fifth row where you get a true 5-row average.
The PARTITION BY EndpointName ensures each API endpoint has its own independent moving average, so slow requests to /api/orders don’t affect the moving average for /api/users and vice-versa.
Detecting Performance Degradation
Moving averages can be useful when you compare them against individual values to identify anomalies:
SELECT EndpointName,
RequestTime,
DATEDIFF(millisecond, RequestTime, ResponseTime) AS ResponseTimeMs,
AVG(DATEDIFF(millisecond, RequestTime, ResponseTime))
OVER (PARTITION BY EndpointName
ORDER BY RequestTime
ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS MovingAvg5Requests,
CASE
WHEN DATEDIFF(millisecond, RequestTime, ResponseTime) >
AVG(DATEDIFF(millisecond, RequestTime, ResponseTime))
OVER (PARTITION BY EndpointName
ORDER BY RequestTime
ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) * 1.2
THEN 'Slow'
ELSE 'Normal'
END AS PerformanceStatus
FROM APIRequests
ORDER BY EndpointName, RequestTime;Result:
EndpointName RequestTime ResponseTimeMs MovingAvg5Requests PerformanceStatus
------------ ------------------------ -------------- ------------------ -----------------
/api/orders 2025-11-21T10:02:00.000Z 1100 1100 Normal
/api/orders 2025-11-21T10:07:00.000Z 1250 1175 Normal
/api/orders 2025-11-21T10:12:00.000Z 980 1110 Normal
/api/orders 2025-11-21T10:17:00.000Z 1150 1120 Normal
/api/orders 2025-11-21T10:22:00.000Z 1300 1156 Normal
/api/orders 2025-11-21T10:27:00.000Z 1450 1226 Normal
/api/orders 2025-11-21T10:32:00.000Z 1520 1280 Normal
/api/orders 2025-11-21T10:37:00.000Z 1600 1404 Normal
/api/users 2025-11-21T10:00:00.000Z 850 850 Normal
/api/users 2025-11-21T10:05:00.000Z 920 885 Normal
/api/users 2025-11-21T10:10:00.000Z 780 850 Normal
/api/users 2025-11-21T10:15:00.000Z 1200 937 Slow
/api/users 2025-11-21T10:20:00.000Z 1150 980 Normal
/api/users 2025-11-21T10:25:00.000Z 1500 1110 Slow
/api/users 2025-11-21T10:30:00.000Z 1680 1262 Slow
/api/users 2025-11-21T10:35:00.000Z 1820 1470 Slow
/api/users 2025-11-21T10:40:00.000Z 1750 1580 Normal
/api/users 2025-11-21T10:45:00.000Z 1900 1730 Normal
This flags requests that are more than 20% slower than the recent moving average, helping you identify when individual requests are outliers versus when overall performance is trending worse. I used * 1.2 to calculate this. You can increase or decrease it as required. For example, you could use * 1.5 to flag requests that are 50% slower.
Comparing Different Window Sizes
You can calculate multiple moving averages with different window sizes to see both short-term and longer-term trends:
SELECT EndpointName,
RequestTime,
DATEDIFF(millisecond, RequestTime, ResponseTime) AS ResponseTimeMs,
AVG(DATEDIFF(millisecond, RequestTime, ResponseTime))
OVER (PARTITION BY EndpointName
ORDER BY RequestTime
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg3Requests,
AVG(DATEDIFF(millisecond, RequestTime, ResponseTime))
OVER (PARTITION BY EndpointName
ORDER BY RequestTime
ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS MovingAvg5Requests,
AVG(DATEDIFF(millisecond, RequestTime, ResponseTime))
OVER (PARTITION BY EndpointName
ORDER BY RequestTime
ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) AS MovingAvg10Requests
FROM APIRequests
ORDER BY EndpointName, RequestTime;Result:
EndpointName RequestTime ResponseTimeMs MovingAvg3Requests MovingAvg5Requests MovingAvg10Requests
------------ ------------------------ -------------- ------------------ ------------------ -------------------
/api/orders 2025-11-21T10:02:00.000Z 1100 1100 1100 1100
/api/orders 2025-11-21T10:07:00.000Z 1250 1175 1175 1175
/api/orders 2025-11-21T10:12:00.000Z 980 1110 1110 1110
/api/orders 2025-11-21T10:17:00.000Z 1150 1126 1120 1120
/api/orders 2025-11-21T10:22:00.000Z 1300 1143 1156 1156
/api/orders 2025-11-21T10:27:00.000Z 1450 1300 1226 1205
/api/orders 2025-11-21T10:32:00.000Z 1520 1423 1280 1250
/api/orders 2025-11-21T10:37:00.000Z 1600 1523 1404 1293
/api/users 2025-11-21T10:00:00.000Z 850 850 850 850
/api/users 2025-11-21T10:05:00.000Z 920 885 885 885
/api/users 2025-11-21T10:10:00.000Z 780 850 850 850
/api/users 2025-11-21T10:15:00.000Z 1200 966 937 937
/api/users 2025-11-21T10:20:00.000Z 1150 1043 980 980
/api/users 2025-11-21T10:25:00.000Z 1500 1283 1110 1066
/api/users 2025-11-21T10:30:00.000Z 1680 1443 1262 1154
/api/users 2025-11-21T10:35:00.000Z 1820 1666 1470 1237
/api/users 2025-11-21T10:40:00.000Z 1750 1750 1580 1294
/api/users 2025-11-21T10:45:00.000Z 1900 1823 1730 1355
When the 3-request average starts diverging from the 10-request average, it signals that recent performance is changing compared to the broader trend. If the short-term average is rising while the long-term average is stable, performance is degrading. If the short-term average is falling while the long-term is stable, performance is improving.
Using Moving Averages for Capacity Planning
Moving averages can help identify trends that inform capacity decisions:
WITH RequestStats AS (
SELECT EndpointName,
RequestTime,
DATEDIFF(millisecond, RequestTime, ResponseTime) AS ResponseTimeMs,
AVG(DATEDIFF(millisecond, RequestTime, ResponseTime))
OVER (PARTITION BY EndpointName
ORDER BY RequestTime
ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS MovingAvg5Requests,
ROW_NUMBER() OVER (PARTITION BY EndpointName ORDER BY RequestTime) AS RequestSequence
FROM APIRequests
)
SELECT EndpointName,
RequestTime,
ResponseTimeMs,
MovingAvg5Requests,
MovingAvg5Requests - LAG(MovingAvg5Requests)
OVER (PARTITION BY EndpointName ORDER BY RequestTime) AS MovingAvgChange
FROM RequestStats
WHERE RequestSequence >= 5 -- Only show rows with full 5-request windows
ORDER BY EndpointName, RequestTime;Result:
EndpointName RequestTime ResponseTimeMs MovingAvg5Requests MovingAvgChange
------------ ------------------------ -------------- ------------------ ---------------
/api/orders 2025-11-21T10:22:00.000Z 1300 1156 null
/api/orders 2025-11-21T10:27:00.000Z 1450 1226 70
/api/orders 2025-11-21T10:32:00.000Z 1520 1280 54
/api/orders 2025-11-21T10:37:00.000Z 1600 1404 124
/api/users 2025-11-21T10:20:00.000Z 1150 980 null
/api/users 2025-11-21T10:25:00.000Z 1500 1110 130
/api/users 2025-11-21T10:30:00.000Z 1680 1262 152
/api/users 2025-11-21T10:35:00.000Z 1820 1470 208
/api/users 2025-11-21T10:40:00.000Z 1750 1580 110
/api/users 2025-11-21T10:45:00.000Z 1900 1730 150
In this example, the MovingAvgChange column shows how much the moving average changed from the previous request. Consistent positive values indicate sustained performance degradation that might require infrastructure attention, while negative values show improvement.
Quick Summary
Moving averages with DATEDIFF() provide a smoothed view of duration trends that helps distinguish meaningful patterns from random noise. They can be invaluable for monitoring systems where you need to understand whether recent performance represents a temporary blip or a sustained trend requiring action.