Moving average
Given a table of dates and daily values, retrieve their moving 5-day average:
DROP TABLE IF EXISTS t;
CREATE TABLE t (dt DATE, qty INT);
INSERT INTO t VALUES ('2025-1-1',5),
('2025-1-2',6),
('2025-1-3',7),
('2025-1-4',8),
('2025-1-5',9),
('2025-1-6',10),
('2025-1-7',11),
('2025-1-8',12),
('2025-1-9',13);
SELECT
t1.dt,
( SELECT SUM(t2.qty) / COUNT(t2.qty)
FROM t AS t2
WHERE DATEDIFF(t1.dt, t2.dt) BETWEEN 0 AND 4
) AS '5dayMovingAvg'
FROM t AS t1
ORDER BY t1.dt;
This generalises easily to moving averages for multiple items:
DROP TABLE IF EXISTS t;
CREATE TABLE t (item int, dt DATE, qty INT);
INSERT INTO t VALUES (1,'2025-1-1',5),
(1,'2025-1-2',6),
(1,'2025-1-3',7),
(1,'2025-1-4',8),
(1,'2025-1-5',9),
(1,'2025-1-6',10),
(1,'2025-1-7',11),
(1,'2025-1-8',12),
(1,'2025-1-9',13),
(2,'2025-1-1',6),
(2,'2025-1-2',7),
(2,'2025-1-3',8),
(2,'2025-1-4',9),
(2,'2025-1-5',10),
(2,'2025-1-6',11),
(2,'2025-1-7',12),
(2,'2025-1-8',13),
(2,'2025-1-9',14);
SELECT
t1.item,t1.dt,
( SELECT SUM(t2.qty) / COUNT(t2.qty)
FROM t AS t2
WHERE item=t1.item AND DATEDIFF(t1.dt, t2.dt) BETWEEN 0 AND 4
) AS '5dayMovingAvg'
FROM t AS t1
GROUP BY t1.item,t1.dt;
+------+------------+---------------+
| item | dt | 5dayMovingAvg |
+------+------------+---------------+
| 1 | 2007-01-01 | 5.0000 |
| 1 | 2007-01-02 | 5.5000 |
| 1 | 2007-01-03 | 6.0000 |
| 1 | 2007-01-04 | 6.5000 |
| 1 | 2007-01-05 | 7.0000 |
| 1 | 2007-01-06 | 8.0000 |
| 1 | 2007-01-07 | 9.0000 |
| 1 | 2007-01-08 | 10.0000 |
| 1 | 2007-01-09 | 11.0000 |
| 2 | 2007-01-01 | 6.0000 |
| 2 | 2007-01-02 | 6.5000 |
| 2 | 2007-01-03 | 7.0000 |
| 2 | 2007-01-04 | 7.5000 |
| 2 | 2007-01-05 | 8.0000 |
| 2 | 2007-01-06 | 9.0000 |
| 2 | 2007-01-07 | 10.0000 |
| 2 | 2007-01-08 | 11.0000 |
| 2 | 2007-01-09 | 12.0000 |
+------+------------+---------------+
or more simply...
SELECT t1.item,t1.dt, AVG(t2.qty) AS 5DayAvg
FROM t t1
JOIN t t2 ON t1.item=t2.item AND DATEDIFF(t1.dt, t2.dt) BETWEEN 0 AND 4
GROUP BY t1.item,t1.dt;