SQL

# Find Year-on-Year Growth Rate

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.

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.