SQL

Find Year-on-Year Growth Rate

In: SQL

This is a common interview question that you MUST nail!

The formula is (value - last_year_value) / last_year_value. You can calculate last_year_value as
πŸ“Œ 𝙻𝙰𝙢(πšŸπšŠπš•πšžπšŽ, 𝟷) πš˜πšŸπšŽπš› (πš˜πš›πšπšŽπš› πš‹πš’ πšŸπšŠπš•πšžπšŽ)

𝙻𝙰𝙢(πšŸπšŠπš•πšžπšŽ,𝟷) gets the value from the previous row. 𝙻𝙰𝙢(πšŸπšŠπš•πšžπšŽ,𝟸) gets the value from the row before the previous row.

STEPS:
1️⃣ Use 𝙻𝙰𝙢 to get last year's revenue. Then, find difference between this year and last year's revenue.
2️⃣ π™²π™°πš‚πšƒ all INT to FLOAT. Calculate the growth rate by dividing the difference by last year's revenue.

Caution! Casting integer to float is important for growth rates of integer columns. Otherwise, integer division will occur (instead of float division).

What's the difference? Integer division 1/2=0, while float division 1/2=0.5.

πŸ‘‡ Full example below.

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.