SQL Syntax Best Practices: How to Structure Your SQL Code
ฝัง
- เผยแพร่เมื่อ 7 ก.ค. 2024
- SQL is not just about writing queries, you also need to make sure that your queries are performant, fast, and readable. This video shows you the best practices for structuring SQL queries. Even when your SQL code works correctly, it can still be improved, in particular when it comes to performance and readability.
Link to the question to follow along with me: platform.stratascratch.com/co...
______________________________________________________________________
👉 Subscribe to my channel: bit.ly/2GsFxmA
👉 Playlist for more data science interview questions and answers: bit.ly/3jifw81
👉 Playlist for data science interview tips: bit.ly/2G5hNoJ
👉 Practice more real data science interview questions: platform.stratascratch.com/co...
______________________________________________________________________
Timeline:
Intro: (0:00)
Example Question: (0:40)
Initial Solution to Refactor: (2:51)
Remove Multiple Nested Queries: (3:30)
Ensure Consistent Aliases: (7:12)
Remove Unnecessary ORDER BY Clauses: (10:32)
Remove Unnecessary Subqueries and CTEs: (11:18)
HAVING vs WHERE: (12:48)
Formatting: (14:23)
Conclusion: (15:36)
______________________________________________________________________
About The Platform:
I'm using StrataScratch (platform.stratascratch.com/co..., a platform that allows you to practice real data science interview questions. There are over 1000+ interview questions that cover coding (SQL and python), statistics, probability, product sense, and business cases.
So, if you want more interview practice with real data science interview questions, visit platform.stratascratch.com/co.... All questions are free and you can even execute SQL and python code in the IDE, but if you want to check out the solutions from me or from other users, you can use ss15 for a 15% discount on the premium plans.
______________________________________________________________________
Contact:
If you have any questions, comments, or feedback, please leave them here!
Feel free to also email me at nathan@stratascratch.com
______________________________________________________________________
#StructureYourSQLCode
![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)
I've learned so much from your videos and my stratascratch premium subscription in the past year. Thank you thank you thank you! :)
i love this contents, this is so useful to look back our codes! thank you so much Nate !
I love your videos! Hope you get the recognition you deserve brother
I think I could apply all this when I write my queries. Good session
Super helpful! Thank you!
Very helpful video Nate, thanks for doing this.
In love with your videos. I am enjoying them a lot. Greetings from MX! 😀
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!
thanks👍
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.
GREAT VIDEO🔥🔥
great!
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!
This is an amazing video, it is sooooooooooo helpful!! :)
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. =)
great content!
perfect
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!
thank you for your video, but WHERE should be put before the GROUP BY clause, right?
Yes!
Whoops. Yes! haha sorry about that.
I really appreciate your efforts, Nate.
but please do improve the title of the video according to the question,.
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😊
great
amazing! always it's better to understand best practices with examples like this... It would be awesome to see more like this
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.
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.
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.
nate you are my best friend
Hi @ Nate!
Does StrataScratch have a shortcut for formatting your SQL code?
Such a great video!
I dont understand why group by is necesery in the last select query
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.
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!
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.
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!
🎯 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.
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.
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;