Data Science SQL Interview Question Walkthrough | SQL Sundays #9 | Tina Huang

0:00Your heart can tell you a lot about your health. Apple Watch Series 12 measures your heart rate every five seconds with the most accurate heart rate sensing and awareable. So your vital zap now with heart rate variability can tell you when something is off. And your readiness score can let you know when to rest and when to push. Here are the story in every heartbeat with Apple Watch Series 12. The features described are for wellness purposes only and not for medical use. iPhone 11 or later required based on Apple conducted study of heart rate accuracy August 2026. Visit Apple.com slash Apple Watch Series 12. When you need to build up your team to handle the growing chaos at work use indeed sponsor jobs. It gives your job post the boost it needs to be seen and helps reach people with the right skills, certifications and more spend less time searching and more time actually interviewing candidates who check all your boxes. Listeners of this show will get a $75 sponsor job credit and indeed dot com slash podcast. That's indeed dot com slash podcast terms and conditions apply. Need a hiring hero? Visit the job for indeed sponsor jobs.

1:02The award winning American Airlines Advantage program rewards more than travel. It takes your whole life to new heights. Earn miles when you fly as well as with thousands of your favorite brands and then redeem those miles for flights, upgrades and experiences worldwide. Advantage members also enjoy earlier boarding and free high speed Wi-Fi in flight. Thank you for free and start earning towards your next adventure at a a dot com slash Spotify. Hi, welcome to another sequel Sunday where I go through a walkthrough of a real data science interview question. Let's get started. All right. So this question over here is growth of Airbnb. So let's read the question. Estimate the growth of Airbnb each year using the number of host registered as the growth metric. The rate of growth is calculated by taking the number of host registered in a current year minus the number of host registered in the previous year. Don't buy the number of host registered in the previous year times 100.

2:03Okay. I'll put the year number of hosts in the current year number of host in a previous year and the rate of growth. Rounder rate of growth when nearest percent in order to result in the ascending order based on the year. Assume that the data set consists only of unique hosts, meaning that there are no duplicate hosts listed. Okay. So let's actually check out the table. So this is a really big table. I'm also going on over here. But really though, like what we actually want to find ultimately is the year, the number of host in the current year number of host in the previous year and the growth rate. So we actually don't need most of the columns over here. So this is what the table looks like. It has like a bunch of stuff in it. So really what we need here is just the IDs and we know that the data is only a piece of unique host. So each of these IDs would be one single host and we also need the year that they registered.

3:07Hosts. This column. That's pretty much it. Yeah. We actually don't need any of the other columns here. And though there's a lot that's being shown. Okay. So some of the assumptions that I have looking at this table now and just looking at the question. So my first assumption was answered already. It was like the data set consists only of unique hosts. Okay. So they are only unique hosts. The other assumption that I want to make here is because we're looking at rate of growth. That means in the first year the rate of growth, there would not be rate of growth because there would be no previous year. So if I were an actual interview, I would probably ask the interviewer like what would you like me to do about that scenario? Like should I just leave it as no leave as is or should I get rid of that role or do something like that. So the assumption that I'll be making here is just that I will have a row that will

4:08have some blanks in it and just leave it leave it as it is. That's pretty much the only assumption that I have. So before I get set and actually dive into the grade, I'm going to go through my general strategy of first writing out exactly how I'm going to approach this fashion in English and then go and write the query. Let's first write down what it is that we ultimately want. So we want the year and the number of hosts. I guess I call it num host current and the num host previous. And we also want to grow through rate. So that's the ultimate table that we want. So now how I would approach this question. Okay, so first I'm going to have a CTE over here that is going to just get the year and the number of hosts for that year.

5:09And then after that, I'm going to do a self-joint of this table over here. And that's going to be based on so self-joint that's going to be on. So it's like the previous year is equal to current year minus one. And this is going to be like a left-joint because we want that first row as well. Okay, and then after that, I do the join and then what I would do is get the current year and the number current num, what did I call it? Num host current and the num host previous. And then I also want to get the growth rate. Which would be calculated by current year.

6:14So it would be like num host current minus the num host previous divided by the num host previous. See around that. I'm not too glad by that. So that would meet a growth rate. And then ultimately, I want to get the, I run to round deep row three, two years per cent and order by the current year. Okay, so this looks like the current approach to me. So I was going to go ahead and actually write the query. Oh, I spelled this wrong order. Okay, so to actually write this query, query, I would do like from this table Airbnb,

7:14search details. And I want to select the year which is not called year. It is host, host sits. It's called host, since. And I want to actually extract. Your heart can tell you a lot about your health. Apple Watch Series 12 measures your heart rate every five seconds with the most accurate heart rate sensing and awareable. So your vital zap now with heart rate variability can tell you when something is off. And your readiness score can let you know when to rest and when to push. Here are the story in every heartbeat with Apple Watch Series 12. The features described are for wellness purposes only and not for medical use. iPhone 11 or later required based on Apple conducted study of heart rate accuracy August 2026. Visit apple.com slash apple watch series 12. When you need to build up your team to handle the growing chaos at work, use indeed sponsor jobs. It gives your job post the boost it needs to be seen and helps reach people with the right skills, certifications and more. Spend less time searching and more time actually interviewing candidates who check all your boxes.

8:18Listeners of this show will get a $75 sponsor job credit at indeed.com slash podcast. That's indeed.com slash podcast terms and conditions apply. Need a hiring hero? This is a job for indeed sponsor jobs. The award winning American Airlines Advantage program rewards more than travel. It takes your whole life to new heights. Earn miles when you fly as well as with thousands of your favorite brands. And then redeem those miles for flights, upgrades and experiences worldwide. Advantage members also enjoy earlier boarding and free high speed Wi-Fi in flight. Join for free and start earning towards your next adventure at AA.com slash Spotify. De-year from that because this is like a date. So I want to distract. Ear from. Who sits? I was going to call that.

9:18Here. And then I want to get the number of posts. So that would be I think it's ID and pretty sure it's ID. Yeah, ID. So and then I want to get the count ID. As number of. Registrar that year and we don't need to do like this thing over here because it says that it's mini-coast already. Okay, and then we need to group by one, which is the year over here. So this is going to be a CT. We're going to call this table. Registered. Let's call it register as. Like that. And then we're going to do the joins now. So from registers, let's call that. Current year. Join. Left join. Registered. Previous year.

10:21And we are going to be joining on. So left join. Previous year is going to occur here minus one. So. Current year dot year is equal to previous year dot year minus one. No, plus one. Wait. Oh, no. Did it did it the other way around? Oh, okay. I'm just going to do it the way that I wrote it. Although it doesn't really matter that much. Previous year is equal to current year dot year minus one. Okay. So I did that and then I want to get the current year and I'm host current. I'm host previous as well as the growth rate. Okay. So for that, I would get the current year dot year. And then I want to get the current year dot number host. Uh, we can call that what did I call it? The number host front.

11:21And then we can get the previous year dot number host as num host. previous. And then we want to get the growth rate. So that is the current year dot num host minus the previous year dot number host. divided by the previous year dot num host. What's applied by one. Did I put this face in here? No, I didn't. Okay. And then we want to put parentheses around this and then also just multiply that by 1.00 so that it won't be integer division. And then we can call that. Okay. And then next we have the rounded growth rate and an order by current year. And kind. So to do that, we can just do rounds over here.

12:25That should do by the percent and then we want to order by the year. So order by one, which is the current year. So let me just go over this query and make sure that I did everything correctly. Pull this down a little bit. Uh, let's see. So registers here. Okay, from Airbnb search details, I want to do extra借 from host since the year counting IDs. Here's I get year and number of hosts. And then I'm doing the joint over here. So from registers current year left, join registers previous year. On previous year is equal to current year minus one. That makes sense. And then I'm going to get the current the year of the current year and then get the number of hosts for the current year and number of hosts for the previous year. And then finally, the growth rate, which is current year number host, minus previous year times 1.00 divided by the previous year.

13:27Well, and then multiply that by 100 as the growth rate. And then we run by the growth rate. Okay, so we did that. And then finally, we order by the current year. So I ordered by one. Okay, so that looks correct to me. Figures cost. I see if it works. Hey, nice. Okay, yep, yep, yum. This is what it looks like. Just a double check. Okay, they're expected output. 200, 200, 2000 and time. Number of hosts current, previous year, growth rate. Cool. I mean, that looks correct. Awesome. So in terms of efficiency and optimization here, so we do have a self join over here. You know what? I think we can actually also just do this with a window function.

14:31Yeah, I think we can actually do that with a window function as well. But I don't actually leave that to you guys with homework. Right in the comments below, how you how you would approach this question using a window function. And also, if you think that it will be more efficient or less efficient, than the way that I've done it here using a self join. So before I wrap up this video, I do have an announcement. My SQL for data science and tech interviews course in partnership with 365 data science has launched this week. It's a step by step guide on how to ace the SQL portion of the interview, complete with 10 full mock interviews. So you may actually be wondering what's the difference between that course and the SQL Sundays and all the other free content I already have on this channel. Let me explain. If you follow this free video where I outlined how I passed my own feign interview and practiced the SQL Sundays in the style that I explained in that video, you would be mostly there. I mean, that's how I did it myself and I didn't even have the SQL Sundays writing like that. And I was clearly fine.

15:32This course though is for people who are looking for a little bit more and a resource that I personally wish that I had when I was interviewing. It outlines the exact steps to do to learn enough SQL to pass the interviews. But I think the biggest value is my guided walkthrough and coaching through 10 full mock interviews. Unlike the SQL Sunday mock interview walkthroughs, the 10 full mock interviews has an actual interviewer that asks follow-up questions, gets clarifications and provides feedback. I also walk you through how to install PG Admin, which is for developing and using a Postgres SQL database. And then I provide you the data and SQL script that adds the interview questions data into the database so that you can actually answer to interview questions yourself. It's basically as opposed to the real interview as you can get. My goal is for you to feel like you have done 10 full SQL interviews before your actual interview, so that your actual one will feel just like another mock. And finally, you will also get my direct support if you have any questions. I honestly wish I could answer every single question on YouTube of course.

16:34But unfortunately, if I did that, I would literally not do anything else in my day. So I will be prioritizing answering all questions from the course. I linked a coupon code in the description so you can get it for $9.99 on you to me for a limited amount of time. If you're interested, please do check it out. And I'll see you guys in the next live stream or video. On August 26, visit apple.com slash apple watch series 12.