# 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

date_time | stock_price |
---|---|

2023-08-08 | 13 |

2023-08-09 | 18 |

2023-08-10 | 10 |

2023-08-11 | 10 |

2023-08-12 | 8 |

2023-08-13 | 2 |

2023-08-14 | 6 |

2023-08-15 | 16 |

2023-08-16 | 15 |

2023-08-17 | 12 |

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**

date_time | stock_price | moving_average | volatility |
---|---|---|---|

2023-08-08 | 13 | 6 | 12 |

2023-08-09 | 18 | 7.5 | 1 |

2023-08-10 | 10 | 5.6666666666666675.666 | 3 |

2023-08-11 | 10 | 5.5 | 13 |

2023-08-12 | 8 | 7.4 | 4 |

2023-08-13 | 2 | 7.16666666666667 | 19 |

2023-08-14 | 6 | 8.85714285714286 | 8 |

2023-08-15 | 16 | 8.57142857142857 | -3 |

2023-08-16 | 15 | 8.85714285714286 | -2 |

2023-08-17 | 12 | 11.2857142857143 | -9 |

### 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)

👍