Data Science SQL Interview Question Walkthrough | SQL Sundays #9

Hi friends! Here's a SQL Sunday as part of the SQL Sundays series in celebration of the launch of my SQL for tech and data science interviews course! I know I know it's Tuesday :x Can we let this one slide?

SQL for tech and data science interviews course with 10 mock interviews:

______________________________________________________________________
Timestamps

00:00 intro
00:08 question
01:51 assumptions
02:47 answer
10:43 check answer
11:53 announcement

______________________________________________________________________
Other videos you might be interested in

How to learn data science in 2021:
The resume that got me into FAANG as a data scientist:

______________________________________________________________________
______________________________________________________________________
SQL for tech and data science interviews course with 10 mock interviews:

Real SQL interview question walkthrough series:

Check out StrataScratch for SQL interview prep:
______________________________________________________________________
About me

Hi, my name is Tina and I'm a data scientist at a FAANG company. I was pre-med studying pharmacology at the University of Toronto until I finally accepted that I would make a terrible doctor. I didn't know what to do with myself so I worked for a year as a research assistant for a bioinformatics lab where I learned how to code and became interested in data science. I then did a masters in computer science (MCIT) at the University of Pennsylvania before ending up at my current job in tech :)
______________________________________________________________________
Contact

youtube: youtube comments are by far the best way to get a response from me!

*If you're reaching out through linkedin, please leave a youtube comment just letting me know that you reached out :)

______________________________________________________________________
*The StrataScratch affiliate program give me a small portion of the sales price at no cost to you. I really appreciate your support in helping improve this channel! :)

#SQLSundays #DataScience #SQL #TinaHuang

[SeattleDataGuy]

This is a great walkthrough! I really enjoyed how you took the time to actually outline the solution prior to actually just trying to answer the question.

[atitudeekdam]

It's fortunate that you are in YouTube for guiding all the upcoming data scientists like me !!!!

[renemoch]

You can use a LEAD or LAG window function ordered by year.

[tokkieram]

Im not even a cs student yet, but i still watching that type of videos, im not even a beginner at coding (bc im dont have a computer or laptop), but still, it amazes me, i thought that I would never be interested in something related to math and logistic, but I'm wishing so bad to start college.

Also, good video, I dont know really what you're doing but it really make me feel inspired to follow an IT path.

[hacatu]

There's one tiny issue with the assumptions that it seems not even strata noticed (or at least cared about :p): it's not clear (to me) how the database deals with hosts being removed. Maybe hosts can't be removed even if they become inactive, in which case the number of active hosts would be lower; maybe removed hosts don't show up, in which case there would be times in the past when the number of active hosts would be higher; either way, this probably overestimates the actual growth rate. Of course, as far as I can tell there's no way to tell if a host is still active from the table provided, so this is probably the best info we can get out of it.

[riteshtripathi]

to be honest, before watching your udemy video, i had believed that i would be good in sql, however that perception changed after going through your udemy video, learnt big tone. And your 'sunday sql' videos are cherry on top. 🥳🙏

[jaymaheshwari]

I would prefer using lag instead of increasing complexity by using join. Since during the interview time crunch can happen. The solution shared in the interview is perfect. Just to save time lag() will work best during interview

[thomasgeorgethomas]

Thank you for the thorough step by step walkthrough Tina! For the suggested alternate approach, I think we can use LAG function to calculate the previous year.

[mendelsohnneilchan]

Awesome stuff Tina, looking forward to future technical assessment walkthroughs!

[OpenRehearsal]

buying your course! I’m about halfway through the Google Data Analytics course and I think in some areas it lacks a bit of depth. Definitely keen to explore SQL more with your course :)

[mellowhuang]

I am a big fan of your videos!!! plz upload more videos like this!! Love you

[thankyouthankyou]

Thank you Tina. Leedcode interviews, please:)

[Banana_Boo_]

The self-join method is great! Thanks for sharing

[iqjayfeng]

Love the solution. Not a fan of the interface. I think I heard of a better platform somewhere....

[kanchidoshi]

Thanks Tina for all the SQL questions. I just bought your course on Udemy. Can you also post videos specifically to Data Science position like Python or Statistical Modeling asked by FAANG companies as they seem to be common these days for interviews?

[Freakarium]

since the variable used is host since, the growth rate should be division of cumulative sum of hosts up to current year by cumulative sum of host of previous years.

[mirkwood]

I'm getting a bit lost on how we select "Curr_Year" from registers when there is no "Curr_Year" column, just "Year"

What am I missing on this?

[hodas]

Thanks for your really helpful walkthrough SQL videos. Have already checked many of them. Just have a question here: Didn't we need to take care of the division by zero in case there was no previous year for a particular year? like not just the first year, but in later years, I think you assumed that the data has continuously contains all the years. Thanks again Tina.

[guyii]

seriously grateful for you and for taking the time to do this.

[calgaryyipee]

This series was very helpful in my interview. Happy to share I passed it.