Calculate moving average - SQL

A moving average is a calculation using a subset of a larger set that is index centered and bounded by a N adjacent values.

For example say you have the following values

Now you would like to calculate the moving average for a every day on the past week (7 days) to get this. Volatility is the measure of the last element in the partition minus the first element of the partition as if the price is 13 today but 7 days ago it was 1 that means a positive volatility of 12

How to mimic this with SQL only

Here I am using sqlite to generate 365 days fake data on a daily stock price that will range between 0 and 20 randomly.

SELECT date_time,
       stock_price,
       AVG(stock_price) OVER (ORDER BY date_time ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS      moving_average,
       last_value(stock_price) OVER (ORDER BY date_time ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) -
       first_value(stock_price) OVER (ORDER BY date_time ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) volatility
FROM (WITH RECURSIVE cnt(x) AS
                         (SELECT 1
                          UNION ALL
                          SELECT x + 1
                          FROM cnt
                          LIMIT 365)
      SELECT date(strftime('%Y-%m-%d', 'now'), '-' || x || ' days') as date_time, (abs(random() % 20)) as stock_price
      FROM cnt) as stocks;

Breaking it apart I use a recursive Common Table Expression to generate 365 random days of stock price from today backwards

WITH RECURSIVE cnt(x) AS
(
	SELECT 1
	UNION ALL
	SELECT x + 1
	FROM cnt
	LIMIT 365
)
SELECT date(strftime('%Y-%m-%d', 'now'), '-' || x || ' days') as date_time, (abs(random() % 20)) as stock_price
FROM cnt

Here use the sqlite function of strftime with a parameter of now and formatted to an ISO Date. Then use the random() function mode by 20 (it will +/- 20) and take the absolute value of that to make sure all the stock prices are positive.

Compute the windowed average by using the OVER function and specifying the rows between using PRECEDING and CURRENT ROW and send that data into the AVG function

AVG(stock_price) OVER (ORDER BY date_time ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_average

Then for the volatility use the LAST_VALUE and FIRST_VALUE functions to grab the data points of the window

last_value(stock_price) OVER (ORDER BY date_time ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

first_value(stock_price) OVER (ORDER BY date_time ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

👍