Thanks. I learned a lot. I'm pretty new to the industry. I am working with SQL every day so videos that start with "what is SQL" and "this is a SELECT statement" are really not what I need but I am not in the level to write good/optimal code. The video was exactly what I needed. And not too long and not too short. Everything structured well.
🎯 Key Takeaways for quick navigation: 00:00 *📝 Importance of Code Structure* - Understanding the importance of code structure for comprehension and logic, - Exploring a real SQL interview question to demonstrate code structuring, - Highlighting the significance of well-structured code in job interviews and professional work environments. 02:41 *🛠️ Refactoring Nested Queries* - Refactoring nested queries for clarity and improved logic flow, - Demonstrating the use of common table expressions (CTEs) for better code organization, - Emphasizing the importance of understanding and manipulating data before applying additional logic. 07:14 *🔄 Ensuring Consistent Aliases* - Utilizing consistent and descriptive aliases for tables and columns, - Improving code readability and comprehension through clear naming conventions, - Highlighting the significance of explicitness in code for future reference and collaboration. 10:42 *📊 Eliminating Unnecessary ORDER BY Clauses* - Streamlining code by reducing redundant ORDER BY clauses, - Optimizing code execution for efficiency and performance, - Emphasizing the importance of minimizing unnecessary code execution for code optimization. 12:47 *🧹 Simplifying Code with CASE Statements* - Simplifying code logic using CASE statements for concise and efficient code, - Demonstrating optimization by reducing the number of common table expressions (CTEs), - Streamlining code structure for improved readability and maintenance. 13:02 *🔍 Choosing Between HAVING and WHERE Clauses* - Differentiating between HAVING and WHERE clauses based on usage and optimization, - Opting for WHERE clauses over HAVING clauses for simpler and faster query execution, - Highlighting the importance of understanding SQL nuances for code optimization.
Nope! I made a mistake! I should have ran the code to double check to see if it worked. All you have to do is place the WHERE clause before the GROUP BY and it's fixed.
@@stratascratch The question asked 'Include only records where non-paying customers have more downloads than paying customers" - this may imply we need to use WHERE clause first to filter individual records, then aggregate results with GROUP BY date.
Thank you as this helped me think differently around optimization, and that it's more intuitive than big and scary. At work I review a lot of other's queries (internal & client) and code readability is *always* an issue. Drives me insane as I always have to rewrite/reformat them before I even bother trying to understand them.
great video but as a a rule of thumb run the SQL to see if it works, for example the 'where' clause at the last select was left after the 'group by' , nonetheless the explanation is great! thanks for the video... cheers
Do we really need “group by date, n_nonpaying, n_paying” in the last query? cte returns data already grouped by date: one row - one date. PS. Surprised that it’s on Hard level. Seems pretty basic.
The groupby only facilitates any de-duping needed. I wouldn't expect any duplicates so it's not needed. The groupby might just be an artifact of some other code that was refactored. Agree with you that it's a basic question that would be asked on an interview. Probably a medium level question tbh. But with all the CTEs with joins and case statement, we graded it hard.
Hi Nate, I cannot explain how much appreciation I have to you. Your video is extremely helpful and I have almost watch every single SQL one. All of the questions you explain in a clear way and the platform you built out is also the best. You are my best SQL teacher I want to say. Thank you so much. Keep up making these, we love it.
Thanks so much for the kind words! I'll keep making more content! You'll also see much more from my team. We're hoping to release a lot more videos this year!
Love this video! I’m at the point where I can get through a lot of the problems, but the code is a mess and way longer than the optimized solution. It’s been a challenge to “think in SQL” to write a better code to begin with.
Not many people I know use USING instead of ON so I can't really comment on if it's a good idea or not. Out of everyone I've ever interviewed for a job, I don't think anyone has ever used USING.
Quick question (I've just started with sql). From what i learned so far for case when...then ... statement, after the "then" comes labeling (strings; all examples were using case when for binning). In your code i saw something else (dowlnds.downloads) ...what did you do here? (Is it also a labeling but with a downlds reference, or...?)
I'm just referencing the ms_download_facts table. I'm calling it downlds as you can see from the JOIN statement. So when I am referencing columns, SQL knows what table the column is in.
@@stratascratch Got it. I was able to replicate this on some other exercise. You are referencing the downloads (from the downlds) column so as SUM knows what to ... sum. Can you please tell me, if we were to have 2 columns (something to sum and something to count) how would the case when look like? Would you just write 4 case when(s)? (2 with sums for paying & non_paying and 2 with counts for paying & non_paying)... or is it possible to optimize further into 2 case when(s)
@@diaconescutiberiu7535 It would be 4 cases just as you mentioned. You can't combine sum and counts when writing the cases. And you still need to split paying and non-paying so it's 4 cases in the end.
Try mode analytics SQL tutorial for a free tutorial. That's how I learned how to write window functions. Then try StrataScratch to practice and get better. Good luck!
nate!!! i was looking up sql videos and thought i saw a familiar face from high school 😆 nice channel, very helpful! i’m not doing data science by title but wanted to get better at sql. 💖 janelle
Wow what a blast from the past. You got a great channel yourself. Way better than mine! If you want to learn SQL, check out my platform =) We'll start resuming the SQL videos next month so I hope you check back. Also, LeetCode is another great platform with a SQL component.
Loved this video❤️. Everything is on point! I really liked the optimization techniques using WITH, CASE statements! Looking forward to more videos on SQL. Thanks a ton😊
Really great optimizing techniques! These are some of the key things that I keep in mind while writing my solutions as well, and I like how you explain every step.
Hey Nate! Kudos to the great series. You are a good tutor. I have a request, can you please make a playlist for machine learning & statistics with python? It would be extremely helpful.
My Solution: with user_table as( select t1.user_id, t2.paying_customer from ms_user_dimension t1 inner join ms_acc_dimension t2 on t1.acc_id=t2.acc_id ), final_res as( select distinct t1.date, t2.paying_customer, sum(downloads) over (partition by date, paying_customer order by date) sum_downloads from ms_download_facts t1 inner join user_table t2 on t1.user_id=t2.user_id) select t1.date, t1.sum_downloads as "non-paying downloads", t2.sum_downloads as "paying downloads" from (select * from final_res where paying_customer='no') t1, (select * from final_res where paying_customer='yes') t2 where t1.date=t2.date and t1.sum_downloads>t2.sum_downloads order by 1 desc;
![Multiple Solutions to Data Scientist Interview Question From Amazon [Rolling Average]](http://i.ytimg.com/vi/GeJUvdkJKEc/mqdefault.jpg)
![Multiple Solutions to Data Scientist Interview Question From Amazon [Rolling Average]](/img/tr.png)
![Solving Complex SQL Interview Questions [How to organize lengthy code solutions]](http://i.ytimg.com/vi/vLjAG9eXkcU/mqdefault.jpg)
Thanks. I learned a lot. I'm pretty new to the industry. I am working with SQL every day so videos that start with "what is SQL" and "this is a SELECT statement" are really not what I need but I am not in the level to write good/optimal code. The video was exactly what I needed. And not too long and not too short. Everything structured well.
thank you for your video, but WHERE should be put before the GROUP BY clause, right?
Yes!
Whoops. Yes! haha sorry about that.
🎯 Key Takeaways for quick navigation:
00:00 *📝 Importance of Code Structure*
- Understanding the importance of code structure for comprehension and logic,
- Exploring a real SQL interview question to demonstrate code structuring,
- Highlighting the significance of well-structured code in job interviews and professional work environments.
02:41 *🛠️ Refactoring Nested Queries*
- Refactoring nested queries for clarity and improved logic flow,
- Demonstrating the use of common table expressions (CTEs) for better code organization,
- Emphasizing the importance of understanding and manipulating data before applying additional logic.
07:14 *🔄 Ensuring Consistent Aliases*
- Utilizing consistent and descriptive aliases for tables and columns,
- Improving code readability and comprehension through clear naming conventions,
- Highlighting the significance of explicitness in code for future reference and collaboration.
10:42 *📊 Eliminating Unnecessary ORDER BY Clauses*
- Streamlining code by reducing redundant ORDER BY clauses,
- Optimizing code execution for efficiency and performance,
- Emphasizing the importance of minimizing unnecessary code execution for code optimization.
12:47 *🧹 Simplifying Code with CASE Statements*
- Simplifying code logic using CASE statements for concise and efficient code,
- Demonstrating optimization by reducing the number of common table expressions (CTEs),
- Streamlining code structure for improved readability and maintenance.
13:02 *🔍 Choosing Between HAVING and WHERE Clauses*
- Differentiating between HAVING and WHERE clauses based on usage and optimization,
- Opting for WHERE clauses over HAVING clauses for simpler and faster query execution,
- Highlighting the importance of understanding SQL nuances for code optimization.
I've learned so much from your videos and my stratascratch premium subscription in the past year. Thank you thank you thank you! :)
Hi Nate, are you sure we can use where clause after group by clause ? [HAVING vs WHERE: (12:48)]
Nope! I made a mistake! I should have ran the code to double check to see if it worked. All you have to do is place the WHERE clause before the GROUP BY and it's fixed.
@@stratascratch The question asked 'Include only records where non-paying customers have more downloads than paying customers" - this may imply we need to use WHERE clause first to filter individual records, then aggregate results with GROUP BY date.
I love your videos! Hope you get the recognition you deserve brother
Thank you as this helped me think differently around optimization, and that it's more intuitive than big and scary.
At work I review a lot of other's queries (internal & client) and code readability is *always* an issue. Drives me insane as I always have to rewrite/reformat them before I even bother trying to understand them.
Great to hear!
great video but as a a rule of thumb run the SQL to see if it works, for example the 'where' clause at the last select was left after the 'group by' , nonetheless the explanation is great! thanks for the video... cheers
Yea you're right. I totally forgot to run the SQL query at the end to see if it worked. =)
I think I could apply all this when I write my queries. Good session
Do we really need “group by date, n_nonpaying, n_paying” in the last query? cte returns data already grouped by date: one row - one date.
PS. Surprised that it’s on Hard level. Seems pretty basic.
The groupby only facilitates any de-duping needed. I wouldn't expect any duplicates so it's not needed. The groupby might just be an artifact of some other code that was refactored. Agree with you that it's a basic question that would be asked on an interview. Probably a medium level question tbh. But with all the CTEs with joins and case statement, we graded it hard.
@@stratascratch Thanks for prompt response, Nate. Highly appreciate what you do on your channel.
In love with your videos. I am enjoying them a lot. Greetings from MX! 😀
i love this contents, this is so useful to look back our codes! thank you so much Nate !
Hi Nate, I cannot explain how much appreciation I have to you. Your video is extremely helpful and I have almost watch every single SQL one. All of the questions you explain in a clear way and the platform you built out is also the best. You are my best SQL teacher I want to say. Thank you so much. Keep up making these, we love it.
Thanks so much for the kind words! I'll keep making more content! You'll also see much more from my team. We're hoping to release a lot more videos this year!
Love this video! I’m at the point where I can get through a lot of the problems, but the code is a mess and way longer than the optimized solution. It’s been a challenge to “think in SQL” to write a better code to begin with.
That'll come with time and experience. The initial code is always a mess. Don't worry too much about it. Just optimize at a later time!
Very helpful video Nate, thanks for doing this.
Super helpful! Thank you!
Hi @ Nate!
Does StrataScratch have a shortcut for formatting your SQL code?
Hello. I prefer to use USING, instead of ON in INNER JOIN string. Do think it's a good idea?
Not many people I know use USING instead of ON so I can't really comment on if it's a good idea or not. Out of everyone I've ever interviewed for a job, I don't think anyone has ever used USING.
great content!
Quick question (I've just started with sql). From what i learned so far for case when...then ... statement, after the "then" comes labeling (strings; all examples were using case when for binning). In your code i saw something else (dowlnds.downloads) ...what did you do here? (Is it also a labeling but with a downlds reference, or...?)
I'm just referencing the ms_download_facts table. I'm calling it downlds as you can see from the JOIN statement. So when I am referencing columns, SQL knows what table the column is in.
@@stratascratch Got it. I was able to replicate this on some other exercise. You are referencing the downloads (from the downlds) column so as SUM knows what to ... sum.
Can you please tell me, if we were to have 2 columns (something to sum and something to count) how would the case when look like? Would you just write 4 case when(s)? (2 with sums for paying & non_paying and 2 with counts for paying & non_paying)... or is it possible to optimize further into 2 case when(s)
@@diaconescutiberiu7535 It would be 4 cases just as you mentioned. You can't combine sum and counts when writing the cases. And you still need to split paying and non-paying so it's 4 cases in the end.
I dont understand why group by is necesery in the last select query
Can anyone help me , which is the best site to learn the syntax for window functions
Try mode analytics SQL tutorial for a free tutorial. That's how I learned how to write window functions. Then try StrataScratch to practice and get better. Good luck!
GREAT VIDEO🔥🔥
This is an amazing video, it is sooooooooooo helpful!! :)
I really appreciate your efforts, Nate.
but please do improve the title of the video according to the question,.
great
amazing! always it's better to understand best practices with examples like this... It would be awesome to see more like this
nate!!! i was looking up sql videos and thought i saw a familiar face from high school 😆 nice channel, very helpful! i’m not doing data science by title but wanted to get better at sql. 💖 janelle
Wow what a blast from the past. You got a great channel yourself. Way better than mine! If you want to learn SQL, check out my platform =) We'll start resuming the SQL videos next month so I hope you check back. Also, LeetCode is another great platform with a SQL component.
thanks👍
great!
Loved this video❤️. Everything is on point! I really liked the optimization techniques using WITH, CASE statements! Looking forward to more videos on SQL. Thanks a ton😊
Really great optimizing techniques! These are some of the key things that I keep in mind while writing my solutions as well, and I like how you explain every step.
perfect
nate you are my best friend
Such a great video!
Cleaner if you use an IF statement instead of CASE!
IF statement in SQL?
Hey Nate! Kudos to the great series. You are a good tutor. I have a request, can you please make a playlist for machine learning & statistics with python? It would be extremely helpful.
We are definitely doing this soon!
My Solution:
with user_table as(
select t1.user_id, t2.paying_customer
from ms_user_dimension t1
inner join ms_acc_dimension t2 on t1.acc_id=t2.acc_id
),
final_res as(
select distinct t1.date,
t2.paying_customer,
sum(downloads) over (partition by date, paying_customer order by date) sum_downloads
from ms_download_facts t1
inner join user_table t2 on t1.user_id=t2.user_id)
select t1.date,
t1.sum_downloads as "non-paying downloads",
t2.sum_downloads as "paying downloads"
from (select * from final_res where paying_customer='no') t1, (select * from final_res where paying_customer='yes') t2
where t1.date=t2.date and t1.sum_downloads>t2.sum_downloads
order by 1 desc;