SQL

Create Running Weighted Average

Combine ROW_NUMBER, self JOIN, and CASE WHEN for that
In: SQL


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!

More from Travis Tang
Use ARRAY_AGG to flatten columns to lists.
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
Pivot Table (From Long to Wide)
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.
Your link has expired.
Success! Check your email for magic link to sign-in.
Success! Your billing info has been updated.
Your billing was not updated.