Great vid. One minor thing - please don't teach people to name their CTEs t1, t2,t3 etc or alias their tables in joins with t1,t2,t3. In my opinion you should always try to give the CTEs names that somewhat can explain whats going on.
Damn, been writing SQL for years and just starting to use more CTEs in the past year instead of subqueries, but never realized that you could make the second query dependent on the results of the first. That's huge! Clicked all the thumbs and bells and stuff.
for second question you could have easily used a mix of dense_rank and row_number ------------> dense_rank() over(partition by user_id order by song_id) r_in_sng ,row_number () over(partition by user_id,song_id order by created_at) r_in_grp ----------> where r_in_sng = 3 and r_in_grp = 1
if we order by score and then use lag/lead window function to get the prev/next score and then calculate the difference and go on from there. When we have ordered the complete table by score, the minimum difference is going to be with the next candidate's score or with the previous candidates's score, no other options. Can we do something like this?
@12:14 Closest SAT Scores... You do not need to do a "join of all possible combinations of students" - this is completely overkill since you know the person with the best score and lowest score can't possible have the smallest difference in scores. More generally, any pair of students with another student between them in the rankings can't possibly be the smallest difference in scores. Therefore, instead of doing this crazy expensive join, we can just use LEAD or LAG to get the scores of the NEXT/PREVIOUS score: WITH diff AS ( SELECT student AS one_student, LEAD(student) OVER (ORDER BY score) AS other_student, ABS(score - LEAD(score) OVER (ORDER BY score)) AS score_diff FROM scores ) SELECT one_student, other_student, score_diff FROM diff WHERE score_diff IS NOT NULL ORDER BY score_diff ASC, LEAST(one_student, other_student) ASC LIMIT 1; As a final note, you cannot use the < join as simply as you stated, because the names of the students might be the wrong way around (one_student might be James and other_student might be Alice for one pair and Bob and Sam for the other - but will be ordered Bob first and James second). You've solved this by joining EVERY pair together so you have a (James, Alice) pair and a (Alice James) pair but this is doubling the computation requirements - which is terrible. I've solved this with a LEAST(name1,name2) in the ORDER BY so that the name "higher in the alphabet" is retrievable from either variable.
Personal nit-picky thing, I think in learning and training it's a really good practice to use 'AS' to show that you're using an alias. Again, this isn't something you need to do all the time, but for those unfamiliar with writing and reading SQL, I think it's a big help in understanding the syntax.
Totally accurate statement. Fun fact, most people at meta would just right NIT before a nit-picky comment. We got rid of a lot of these nits by implementing a style guide that was automatically implemented up saving. Reduces the amount of time code is in review and number of people being like, hey can you tab this over 1 more time.
second question now requires returning users who dont have a third song with 'null' for column song and data.. for this you can replace join on second cte to a right join
So 3,1,2 represents the columns in their respective orders. 3 is the abs(lowest score), 1 student name, 2, other student name. It's a short cut instead of writing out the entire column. Is that what you are asking?
This is really coool. I like the 1st problem you solved using group by clause. it never occur to me before. I hope you will continue to publish more sql problems.
Glad you enjoyed it! We shall see, I might have to do more! It's always fun to walk through. Maybe next time I can get someone to do better animations.
I would have answered the first question with a not exists as it's probably the easiest to read and understand what the code is supposed to be doing, good job on the other two questions, I'll have to have a look at the site and have a play myself 😋
Can we do this with Python as well? I am a new data engineer that is trying to get to my next assignment. I need to hurry and upskill on Python by doing. I want to use this site youve used here to practice more SQL as well. I could never get good enough with SQL it seems.
Thanks for putting this out. I heard you on Ken's Nearest Neighbors this past week and it was great to hear more of your backstory. Seattle for the win!!!
Facebook, not too much. Understand basic data structures and honestly...not a ton of algorithms. Lyft is pretty ds&a heavy from what I have heard, amazon had 0 coding questions.
Hey Ben! I hope you're extensively looking into the IBM Data Engineering Professional Certificate on Coursera, as it provides an end to end solution using everything from SQL to Airflow to basic Warehousing. Hoping for a video detailing this soon.
I really liked this one, more than others! Didn't know about that site. I'll use it for sure. And seeing that, kind of, practical class, helps me a lot figure out something that maybe I didn't know so far. Thanks Ben!
That could be a solution as well. Sometimes some companies prefer you not use window functions when you are interviewing. I have heard "can you do it without a window function" a few times
We are using the neighborhood has the table on the left so when we join what will happen is if there isn't a matching neighborhood_id in the user table then the user id will be null. This is a slightly less explicit solution which is why I provide the second solution. They are really doing the same thing. Finding the rows where in the user table didn't have a matching neighborhood id.
For the last example why not self join onto the rank ordered by score? i.e. join rank(score) = rank(score) - 1 and then calculate the difference of the scores. Wouldn't a cross join be too resource intensive?
If you like this content, then check out my newsletter! seattledataguy.substack.com/p/the-baseline-data-stack-building Also if you want to try out a low code tool for free, you can try out Rivery here bit.ly/3HMnsuo
Hello Ben, i've been following your data engineer roadmap, im still learning python, is it important to learn flask/django or any python web framework? Will it help me to understand data engineering better?
It's funny to me how a lot of the SQL questions in interviews are things you'll never have to do in your day to day job. They just find a leetcode problem and ask it. So dumb.
I was always told by the senior engineers that using a SELECT DISTINCT is a sign of an inexperienced developer. But they never tell me why, could you possibly help me why that is? Edit: Spelling
I can see why they would say that. There are many reasons DISTINCT isn't thought of highly. It's often used as a bandage. Instead of fixing bad data, we will just shove "distinct' over it and de duplicate the data. DISTINCT is also often an expensive operation. Also, DISTINCT can come off like you don't fully understand the data that is underlying. Again, I think this answer was literally my 3-second response. The distinct is actually not necessary. But when it comes to interviews you will have to be fast for screening rounds. Facebook will try to get you to answer 5 questions in 30 minutes and most people only get to 2 or 3 in my experience. So when putting code in production, sure, avoid distinct but realize it also has its place.
Thank you for your video. Can you please explain the meaning of "Unique song" in question 2? As far as I understand unique song it is a song that was not not singed by other singers before and your query doesn't approach this situation. For example, if Eminem singed a song of Dr Dre that was created in '01/01/2010' in '01/01/2022' , then the created date will be '01/01/2010' but it is not unique at this time. This will be my answer to questions two: WITH UNIQUE_SONG AS( SELECT US.ID, SP.ID , ROW_NUMBER(S[.DATE) OVER (PARTITION BY US.ID ORDER BY SP.DATE) AS 'RN' FROM USERS AS US INNER JOIN SONG AS SP ON SP.ID = US.ID WHERE US.SONG_ID IN( SELECT Song.id FROM SONG as SP group by SP.id HAVIGN DATE = min(SP.date))) SELECT UNIQUE_SONG.* FROM UNIQUE_SONG WHERE RN=3
SQL is so underrated. Could not imaging how do to my job without it.
SQL is always clutch! I love it and probably over use it haha.
Great vid.
One minor thing - please don't teach people to name their CTEs t1, t2,t3 etc or alias their tables in joins with t1,t2,t3.
In my opinion you should always try to give the CTEs names that somewhat can explain whats going on.
Yeah, same. Then you have such queries on your Work place is headache to read!
For the grouping columns Ive noticed most people prefer to use "1, 2, 3" but yes, for ctes an abbreviation that makes sense is good.
Damn, been writing SQL for years and just starting to use more CTEs in the past year instead of subqueries, but never realized that you could make the second query dependent on the results of the first. That's huge! Clicked all the thumbs and bells and stuff.
Thanks for clicking all the things! Hope you find more helpful things!
for second question you could have easily used a mix of dense_rank and row_number ------------> dense_rank() over(partition by user_id order by song_id) r_in_sng ,row_number () over(partition by user_id,song_id order by created_at) r_in_grp ----------> where r_in_sng = 3 and r_in_grp = 1
if we order by score and then use lag/lead window function to get the prev/next score and then calculate the difference and go on from there. When we have ordered the complete table by score, the minimum difference is going to be with the next candidate's score or with the previous candidates's score, no other options. Can we do something like this?
Most awaited video. Thanks for this👍
Thanks, hopefully it was helpful!!
What i love about this Interview questions is that no one will ever have a working scenario for such dumb tasks x
I have :) But everyone works with different SQL problems.
@12:14 Closest SAT Scores...
You do not need to do a "join of all possible combinations of students" - this is completely overkill since you know the person with the best score and lowest score can't possible have the smallest difference in scores.
More generally, any pair of students with another student between them in the rankings can't possibly be the smallest difference in scores.
Therefore, instead of doing this crazy expensive join, we can just use LEAD or LAG to get the scores of the NEXT/PREVIOUS score:
WITH diff AS (
SELECT
student AS one_student,
LEAD(student) OVER (ORDER BY score) AS other_student,
ABS(score - LEAD(score) OVER (ORDER BY score)) AS score_diff
FROM scores
)
SELECT
one_student,
other_student,
score_diff
FROM diff
WHERE
score_diff IS NOT NULL
ORDER BY
score_diff ASC,
LEAST(one_student, other_student) ASC
LIMIT 1;
As a final note, you cannot use the < join as simply as you stated, because the names of the students might be the wrong way around (one_student might be James and other_student might be Alice for one pair and Bob and Sam for the other - but will be ordered Bob first and James second). You've solved this by joining EVERY pair together so you have a (James, Alice) pair and a (Alice James) pair but this is doubling the computation requirements - which is terrible.
I've solved this with a LEAST(name1,name2) in the ORDER BY so that the name "higher in the alphabet" is retrievable from either variable.
Great video, thank you Ben!!
Glad you enjoyed it!
Personal nit-picky thing, I think in learning and training it's a really good practice to use 'AS' to show that you're using an alias. Again, this isn't something you need to do all the time, but for those unfamiliar with writing and reading SQL, I think it's a big help in understanding the syntax.
Totally accurate statement. Fun fact, most people at meta would just right NIT before a nit-picky comment. We got rid of a lot of these nits by implementing a style guide that was automatically implemented up saving. Reduces the amount of time code is in review and number of people being like, hey can you tab this over 1 more time.
I don’t understand why greater than failed but less than worked?
Same
Lag or lead would work as well for the third problem
Yup! You could. This video was already getting long so I wanted to avoid rambling too long.
second question now requires returning users who dont have a third song with 'null' for column song and data.. for this you can replace join on second cte to a right join
This was really helpful, specially the join on last problem was a fantastic approach
Thank you! I am glad you found it helpful!
Minor detail - but your question 1 answer 1 assumes that u.id is non-null (which it should be as an id column)
AMAZING VID!
Thank you so much Jay!
Thank u for this video 👋👋👋👋👋👋👋👋👋
Glad you enjoyed it!
How does the order by 3, 2, 1 select the student combination that is higher in the alphabet?
So 3,1,2 represents the columns in their respective orders. 3 is the abs(lowest score), 1 student name, 2, other student name. It's a short cut instead of writing out the entire column. Is that what you are asking?
Why not order by score and lead by 1 and get the diff? Cartesian join must be expensive
This is really coool. I like the 1st problem you solved using group by clause. it never occur to me before. I hope you will continue to publish more sql problems.
Glad you enjoyed it! We shall see, I might have to do more! It's always fun to walk through. Maybe next time I can get someone to do better animations.
I would have answered the first question with a not exists as it's probably the easiest to read and understand what the code is supposed to be doing, good job on the other two questions, I'll have to have a look at the site and have a play myself 😋
Let me know your thoughts on the site!
Can we do this with Python as well? I am a new data engineer that is trying to get to my next assignment. I need to hurry and upskill on Python by doing. I want to use this site youve used here to practice more SQL as well. I could never get good enough with SQL it seems.
I might have to put this on the list of videos!
Thanks for putting this out. I heard you on Ken's Nearest Neighbors this past week and it was great to hear more of your backstory. Seattle for the win!!!
I am so glad you got to see it! Thanks for the comment
How much understanding of Data structure and algorithms is required to crack top product based companies
Facebook, not too much. Understand basic data structures and honestly...not a ton of algorithms. Lyft is pretty ds&a heavy from what I have heard, amazon had 0 coding questions.
Hey Ben! I hope you're extensively looking into the IBM Data Engineering Professional Certificate on Coursera, as it provides an end to end solution using everything from SQL to Airflow to basic Warehousing. Hoping for a video detailing this soon.
I filmed it this week and sent it to my editor
just started this course last week
@@SeattleDataGuy Thank you!!!!
@@BJTangerine how do you like it so far? I was thinking doing this after I finished the IBM Data Analyst.
I really liked this one, more than others! Didn't know about that site. I'll use it for sure. And seeing that, kind of, practical class, helps me a lot figure out something that maybe I didn't know so far.
Thanks Ben!
I am so glad you found this helpful!
Select distinct name from neighbourhoods where id not in ( select distinct meighborhood_id from users)
I believe this should work as well.
Love this kind of content, keep it up
Glad you enjoyed it!
The third question about the SAT would’ve been achieved by lead lag functions as well right?
That could be a solution as well. Sometimes some companies prefer you not use window functions when you are interviewing. I have heard "can you do it without a window function" a few times
For the first question, couldn't a specific neighborhood be null in one row but filled in another row?
We are using the neighborhood has the table on the left so when we join what will happen is if there isn't a matching neighborhood_id in the user table then the user id will be null.
This is a slightly less explicit solution which is why I provide the second solution. They are really doing the same thing. Finding the rows where in the user table didn't have a matching neighborhood id.
I am new to coding and stuff , pls provide best source for learning the SQL.
Maybe my video for great data courses will help ua-cam.com/video/kW8_l57w74g/v-deo.html
As always, very useful video! Thank you!! Just getting my DE interview on Wednesday 🙄🙄
Good luck Alex!
For the last example why not self join onto the rank ordered by score? i.e. join rank(score) = rank(score) - 1 and then calculate the difference of the scores. Wouldn't a cross join be too resource intensive?
First solution may not work if data is unclean, eg if there actually existed a user but his id was accidentally stored as NULL
We can do partition by user name and song and use row _num=3 with order by asc. No need of group by CTE in 2nd question
You mean row_number() over (partition by username, song_id order by created)? Did you test it out?
Because if you do that, all it will do is start counting at 1 at the top of every new song_id.
For the third question we can order by the scores and then use lead() or lag() function to compare the scores
Another great video, thank you.
Glad you liked it!
If you like this content, then check out my newsletter! seattledataguy.substack.com/p/the-baseline-data-stack-building
Also if you want to try out a low code tool for free, you can try out Rivery here bit.ly/3HMnsuo
Thanks very much !
You are welcome!
Hello Ben, i've been following your data engineer roadmap, im still learning python, is it important to learn flask/django or any python web framework? Will it help me to understand data engineering better?
You don't have too. Personally, I think its not a bad skill to have. But you could get away without it.
@@SeattleDataGuy thanks for the reply Ben!
Hey, a pleasure to watch you, thanks for the practice source
Glad you enjoyed it!
Thank you so much bro~
You're welcome!
It's funny to me how a lot of the SQL questions in interviews are things you'll never have to do in your day to day job.
They just find a leetcode problem and ask it. So dumb.
I was always told by the senior engineers that using a SELECT DISTINCT is a sign of an inexperienced developer. But they never tell me why, could you possibly help me why that is?
Edit: Spelling
I can see why they would say that. There are many reasons DISTINCT isn't thought of highly. It's often used as a bandage. Instead of fixing bad data, we will just shove "distinct' over it and de duplicate the data. DISTINCT is also often an expensive operation. Also, DISTINCT can come off like you don't fully understand the data that is underlying. Again, I think this answer was literally my 3-second response. The distinct is actually not necessary. But when it comes to interviews you will have to be fast for screening rounds. Facebook will try to get you to answer 5 questions in 30 minutes and most people only get to 2 or 3 in my experience. So when putting code in production, sure, avoid distinct but realize it also has its place.
@@SeattleDataGuy ah, I see. Thank you so much for your speedy response! I love your videos!
every time you mention naruto in a video. I pause it go like the video and then resume.
I gotta mention him again!
Why would you use SQL for this? Python or JS are much better suited for this type of operation.
Oh! That’s interesting. I would love to hear more on how python and js could do a better job.
Interview query is expensive. Is stratascratch good enough for product companies? I am thinking of getting that for practicing SQL.
I think stratascratch is also solid. I generally start with free things and only go to paid if I need it.
Thank you for your video.
Can you please explain the meaning of "Unique song" in question 2?
As far as I understand unique song it is a song that was not not singed by other singers before and your query doesn't approach this situation.
For example, if Eminem singed a song of Dr Dre that was created in '01/01/2010' in '01/01/2022' , then the created date will be '01/01/2010' but it is not unique at this time.
This will be my answer to questions two:
WITH UNIQUE_SONG AS(
SELECT US.ID, SP.ID , ROW_NUMBER(S[.DATE) OVER (PARTITION BY US.ID ORDER BY SP.DATE) AS 'RN'
FROM USERS AS US
INNER JOIN SONG AS SP
ON SP.ID = US.ID
WHERE US.SONG_ID IN(
SELECT Song.id
FROM SONG as SP
group by SP.id
HAVIGN DATE = min(SP.date)))
SELECT UNIQUE_SONG.*
FROM UNIQUE_SONG
WHERE RN=3