Amazon SQL Interview | Percent Change | FORMAT & LAG Functions

In this video, we will explore how to calculate the percent change by using the LAG function and format.

#sql
#sqlinterview
#dataanalysis

Learn and Practice SQL coding online at DataCamp, check out my affiliate link:

You can practice SQL interview Questions on Stratascratch

[theresaparker]

Love this. You make everything look so simple.

[onlyAI..]

Finally a youtube channel that is not wasting time by teaching basic stuff

[saurabhshrigadi]

1 request, explain a question a little before jumping to code

[streetthinker]

How does one practice with the dataset you using

[sandeepgarad]

Missing left side column in every video please keep that also visible 😊

[nesky]

The code shown in the video will create wrong month over month change if the year is different.

For dates with different year:

With cte as(
Select
format(created_on, 'yyyy-MM') as yr_mn,
sum(value) as total
From purchase
Group by yr_mn
)

Select
yr_mn,
Round(
cast(
total - lag(total) over(order by yr_mn)
as float) / lag(total) over(order by yr_mn), 2
) * 100 as pct
From cte

[Rajag-icxt]

Hi thanks for vedio.
I am having trouble in interview to write sql query, any suggestions

[Suma-qhnn]

Sir I want starting to beginning sql how to write queries plzzz teach me how ur writing this complex queries in seconds plzzz teach me

[Tech_with_Srini]

with cte as(
select year(order_date) as year, FORMAT(order_date, 'MM-yyyy') as month_year, sum(final_price) as tot_price
from orders2
group by year(order_date), FORMAT(order_date,'MM-yyyy')
)

select *,
lag(tot_price, 1) over (order by month_year) as cumm_price from cte
order by year

[Tech_with_Srini]

What if multiple years in the date, it will take 01-2019, 01-2020 as order, so your running total will be wrong