SQL

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

Written by

### Travis Tang

More from Travis Tang
SQL

### Use ARRAY_AGG to flatten columns to lists.

You need to convert a long table of values into a list? This is the most convenient function. π Syntax: ππ΄π»π΄π²π π°πππ°π_π°πΆπΆ(ππππππ) π΅ππΎπΌ πππππ There
SQL

### Generate Histogram Distribution

For a quick exploration of the distribution
SQL

### Pivot Table (From Long to Wide)

Every data scientist, scientist, and engineer should know how to create a pivot table. π²π°ππ΄ ππ·π΄π½ is the best way to do
Great! Youβve successfully signed up.
Welcome back! You've successfully signed in.
You've successfully subscribed to Travis Tang.