Advanced Data Science SQL Interview Question [Amazon] (window functions & aliasing)

This SQL data science interview question was asked by Amazon and will test your date manipulation and window function skills. I’ll cover both the question and walk you through the approach. I’ll also talk about my 4 step approach to solving any data science interview question. This is literally how I would answer every data science interview question and prepare for every data science interview at FAANG companies and others.

______________________________________________________________________

______________________________________________________________________
Timeline:

Intro: (0:00)
Question: (1:18)
4-Step framework to solve the question: (2:02)
Explore underlying data: (2:18)
Identify required columns: (3:45)
Visualize the output: (4:12)
Build solution step-by-step and test: (4:44)
Coding: (4:57)
Format data to YYYY-MM: (6:27)
Calculate current month's revenue: (7:46)
Calculate previous month's revenue: (8:36)
Aggregate to year-month: (10:25)
Implement month-over-month difference formula: (11:26)
Apply a window alias: (13:15)
Clean up formatting: (14:06)
Conclusion: (14:50)
______________________________________________________________________
About The Platform:

______________________________________________________________________
Contact:

If you have any questions, comments, or feedback, please leave them here!
______________________________________________________________________

#datascience​interview #sqlinterviews

[tamaboy]

I love SQL. It's almost like a game.

[Seikenguy]

This was a "Hard" question on stratascratch, but you explained it in a way that one might think it's elementary. Since I've had experiences with teachers explaining these concepts where I understood virtually nothing, I think you're a really great teacher. Thank you!

[ashutoshsingh]

What a great and simple explanation it was. You’re a great teacher.

I hope you will teach more important topics for SQL.

I appreciate your hard work. Thanks

[rollinas]

I'm easy, I see Nate's vid, I click on it.

[bandhammanikanta]

Thank you for putting this video explanation.

but When I searched for this question "Monthly percentage difference stratascratch", youtube suggested me this video but I wasn't sure if this the exact one until I hit the question link from description.
Please change the title according to the question. Thank you.

[rick]

I never knew about a window alias. That is cool. PostgreSQL has a lot of cool features. embedding an update within a with clause allows one to enter and leave a critical section within one SQL statement....

[cabaymau]

This content is absolutely underrated, why didn't I find you sooner :< Pls do more topics on SQL

[KumarSameer]

Thanks for breaking it down in this great detail. It really gives me a structured approach to solving interview SQL questions!

[bandhammanikanta]

The way you explain is excellent, Nate. Keep it up, please. Thank you :)

[vibekdutta]

Solid explanation, looking forward to some more awesome contents, thanks a lot

[flipcase]

This channel is amazing! Very well broken down🙏🙏 thank you

My feedback is please keep making such amazing videos

[saikatdasgupta]

Here is one more approach

with cte as(select to_date(concat(date_part('year', created_at), '-', date_part('month', created_at)), 'yyyy-mm') dt, sum(value) sum1 from sf_transactions group by dt order by dt)

select ct2.dt,
round(((ct2.sum1 - ct1.sum1)/ct1.sum1)*100, 2)
from cte ct1, cte ct2 where
date_part('month', ct2.dt)=date_part('month', ct1.dt)+1
order by dt

[aztxaz]

This is an awesome video! I learned something new. Would love to see more videos like this.

[SuddenlySubtle]

Thank you for the video!

What would happen if a particular month had no values? Then lag of 1 would not work... Self join on month and the calculated version of previous month, in a CTE could work... A month key = year*12 + month...prev month = month - 1

[aleixmp]

This is a really good video. Very clear and easy to understand. Thank you !

[shalinigarg]

Nate! Your efforts are much appreciated 👍
Thank you so much for uploading these helpful videos 🔥 You are the best teacher in SQL series. Love from India 😊

[nareshvadlamudi]

thanks a lot.! great example to cover many topics!

[prabhaker]

What a great insight into the solution besides the solution itself.

[kylehuang]

Great video, an alternative solution is to self join the table but a.month = b.month - 1 (assuming only 2019)

[brianchuck]

Great content.. Keep it coming! If possible please reference differences in syntaxes across the different rdms.For me, the explanation of the double colon helped...as an example.. I use snowflake and terradata