MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table mytable (
  Date	date,
  Value int
);

insert into mytable values
('2023-01-01', 100),
('2023-01-05', 200),
('2023-01-20', 100),
('2023-02-03', 0),
('2023-02-03', 400),
('2023-02-10', 100),
('2023-03-08', 400);

Records: 7  Duplicates: 0  Warnings: 0


SELECT Date, MAX(average) AS '30d_avg'
FROM (
  SELECT Date, AVG(Value) over(ORDER BY Date RANGE BETWEEN INTERVAL '30' DAY PRECEDING AND CURRENT ROW) as average
  FROM mytable
) AS s
GROUP BY Date


Date
30d_avg


2023-01-01
100.0000

2023-01-05
150.0000

2023-01-20
133.3333

2023-02-03
175.0000

2023-02-10
150.0000

2023-03-08
250.0000


SELECT 
   t1.Date,
   AVG(t2.Value) AS 30d_avg
FROM mytable AS t1
INNER JOIN mytable AS t2 ON t2.Date >= DATE_ADD(t1.Date, INTERVAL -30 DAY) 
                         AND t2.Date <= t1.Date
GROUP BY t1.Date
ORDER BY t1.Date


Date
30d_avg


2023-01-01
100.0000

2023-01-05
150.0000

2023-01-20
133.3333

2023-02-03
175.0000

2023-02-10
150.0000

2023-03-08
250.0000


-- equivalent on snowflake
SELECT 
   t1.Date,
   AVG(t2.Value) AS 30d_avg
FROM mytable AS t1
INNER JOIN mytable AS t2 ON t2.Date >=  DATEADD(DAY, -30, t1.Date)  
                         AND t2.Date <= t1.Date
GROUP BY t1.Date
ORDER BY t1.Date

FUNCTION fiddle.DATEADD does not exist

Date	30d_avg
2023-01-01	100.0000
2023-01-05	150.0000
2023-01-20	133.3333
2023-02-03	175.0000
2023-02-10	150.0000
2023-03-08	250.0000