Create Running Weighted Average

Combine ROW_NUMBER, self JOIN, and CASE WHEN for that


The "normal" running average gives equal weight to all data points, regardless of how recent they are.

To give more weight to recent data, use running weighted average.

πŸ“Œ FORMULA: πš πšŽπš’πšπš‘πšπšŽπš_𝚊𝚟𝚐_πš›πšŽπšŸπšŽπš—πšžπšŽ = πšœπšžπš– 𝚘𝚏 (πš›πšŽπšŸπšŽπš—πšžπšŽ * πš πšŽπš’πšπš‘πš), where πš πšŽπš’πšπš‘πš is a value from 0 to 1.

For example, to give 80% weight to the most recent data point and 20% weight to the data point before that, you can use:
πš πšŽπš’πšπš‘πšπšŽπš_𝚊𝚟𝚐_πš›πšŽπšŸπšŽπš—πšžπšŽ = 𝟢.𝟾 * πš›πšŽπšŸπšŽπš—πšžπšŽ + 𝟢.𝟸 * πš›πšŽπšŸπšŽπš—πšžπšŽ

πŸ“Œ STEPS:
1️⃣ Use πšπ™Ύπš†_π™½πš„π™Όπ™±π™΄πš to assign a number to each row in the dataset. (One row = one year)
2️⃣ Perform a πš‚π™΄π™»π™΅ 𝙹𝙾𝙸𝙽 so that the past year's data and the current year's data appear in the same row.
2️⃣ Use a π™²π™°πš‚π™΄ πš†π™·π™΄π™½(πš‚πš„π™Ό) to assign weights to the past year's data.

This works on all flavors of SQL!