Data Science SQL Interview Question and Answer | Airbnb

====== ✅ Details ======

Love it or hate it - solving the SQL question correctly with speed can make or break your interview success.

That's why in this video, I share:

👉 Three tips that helped clients ace SQL rounds in MAANG companies
👉 Daetama - an SQL practice pad with 100 interview questions
👉 A walkthrough of an Airbnb SQL interview question

⭐ Daetama is a new DataInterview product that will allow you to practice code on a web browser. Get access to 100 FAANG-style SQL questions curated and solved by data scientists at Google and Meta.

👍 Make sure to subscribe, like and share!

====== ⏱️ Timestamps ======

00:00 - How to prepare for data science SQL interview questions
00:33 - Tip #1
01:39 - Tip #2
03:19 - Tip #3
04:23 - Airbnb SQL question

====== 📚 Other Useful Contents ======

====== Connect ======

[Aidan_Au]

I have used Daetama for over 1 month now. I practice SQL every day.
I absolutely recommend anyone who wants to get good at data science interviews to get this tool.

[SwatiTirthramGulia]

Your ability to unravel complex problems and present them in simpler terms is truly impressive.

[Soccergods]

I haven't used the Over clause before. It makes things very intuitive if you do use it. Great video!

[fenrir]

Love the video and super helpful! Can you do a video explaining more on tips for gauging when/where to use Subqueries vs Cte for sql code/dashboard efficiency?

[guptabhishek]

At 1:28 you only change the sequnce of code, but SQL already implements where before group by.

How is that changing anything?

[sm]

There is a flaw in the logic. The first month of an year, should also have a last month's rent. That is not getting considered here. This can be resolved by filtering by year in the last step, rather than the first cte. Also coalesce is not required as lag function itself has a parameter(3rd parameter) that can be specified to take care of null values.

[denny]

How do you know when a block of code should be wrapped within a CTE? I understand all the SQL functions and their syntax, but I'm struggling to understand the logical order of how to structure the entire query. When asked a question and there are a number of keywords involved like counts/ranks/averages, how do I know which functions should go within a CTE, and which in the final output query? Just looking for general tips on how to structure a solution.

Thanks.

[gabrieldurkin]

This is either totally wrong or poorly posed/clarified. Firstly, as someone else pointed out WHERE is always evaluated before GROUP BY by the SQL engine. Secondly, LAG only lags the previous row. So if a property has purchases in January March and May the LAG query will return percentage changes from January to March and March to May. My interpretation of month over month delta would be Feb -> March and March-> April. So since property 9 only has purchases in non-consecutive months Jan, March and May, it wont have increasing MoM values at all (percentage change from zero to any value is infinity and from a finite number to zero is -100). Presenter should check his results to see if property 9 should be there at all. If so, he should clarify at the start what he interprets it to mean: "Calculate the percentage change in total rentals for each property for each month in 2022" - i believe his interpretation is at the least... unorthodox. 🙃

[peterr]

Although this was a nice video to watch, AirBnB is not a meaningful business model as not every rental house deserves its price based on the quality and customer experience in my personal perspective.