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.
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.
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?
Thanks Kenny! I would say that the primary difference between subquery vs CTE really boils down to readability, less on the efficiency. When I was a DS at Google, and building dashboards, there was a set of peer review checks done before my SQL code was submitted to Google's production system. One feedback I received was instead of using a series of nested subqueries, decouple it into CTEs to ensure that colleagues can read this clearly. Hope this helps! Others, any thoughts?
@@DataInterviewMakes sense! Can you explain then in efficiency terms why filtering before a join be more efficient than after a join? or vice versa? Thank you!
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.
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. 🙃
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.
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.
Thanks Aidan! -- Dan
Your ability to unravel complex problems and present them in simpler terms is truly impressive.
At 1:28 you only change the sequnce of code, but SQL already implements where before group by.
How is that changing anything?
Exactly. I think it is not a good example too. There is inherit running order of SQL clause
I haven't used the Over clause before. It makes things very intuitive if you do use it. Great video!
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.
Good catch on the lag parameters
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?
Thanks Kenny! I would say that the primary difference between subquery vs CTE really boils down to readability, less on the efficiency. When I was a DS at Google, and building dashboards, there was a set of peer review checks done before my SQL code was submitted to Google's production system. One feedback I received was instead of using a series of nested subqueries, decouple it into CTEs to ensure that colleagues can read this clearly. Hope this helps! Others, any thoughts?
@@DataInterviewMakes sense! Can you explain then in efficiency terms why filtering before a join be more efficient than after a join? or vice versa? Thank you!
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.
Nice Video
Thanks Raju! -- Dan
Get more SQL questions on my RUclips channel. Here is a sample video: ruclips.net/video/h1fkLMRKOWA/видео.htmlsi=CEdEscBOzclrZi4m
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. 🙃
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.