SQL Interview Questions For Data Scientists And Data Engineers - Tips For Practicing SQL Interviews

For the third question we can order by the scores and then use lead() or lag() function to compare the scores

Why not order by score and lead by 1 and get the diff? Cartesian join must be expensive

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?

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.

First solution may not work if data is unclean, eg if there actually existed a user but his id was accidentally stored as NULL

every time you mention naruto in a video. I pause it go like the video and then resume.

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.

Hey, a pleasure to watch you, thanks for the practice source

Another great video, thank you.

The third question about the SAT would’ve been achieved by lead lag functions as well right?

Select distinct name from neighbourhoods where id not in ( select distinct meighborhood_id from users)

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

Thank u for this video 👋👋👋👋👋👋👋👋👋

What i love about this Interview questions is that no one will ever have a working scenario for such dumb tasks x

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 😋

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

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?

Lag or lead would work as well for the third problem

Why would you use SQL for this? Python or JS are much better suited for this type of operation.

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.

AMAZING VID!

Great video, thank you Ben!!

Interview query is expensive. Is stratascratch good enough for product companies? I am thinking of getting that for practicing SQL.

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

Most awaited video. Thanks for this👍

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.

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!!!

How much understanding of Data structure and algorithms is required to crack top product based companies

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!

As always, very useful video! Thank you!! Just getting my DE interview on Wednesday 🙄🙄

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.

I am new to coding and stuff , pls provide best source for learning the SQL.

This was really helpful, specially the join on last problem was a fantastic approach

Love this kind of content, keep it up

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.

SQL is so underrated. Could not imaging how do to my job without it.

How does the order by 3, 2, 1 select the student combination that is higher in the alphabet?

For the first question, couldn't a specific neighborhood be null in one row but filled in another row?

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.