Data Science SQL Interview Question Walkthrough (real interview style) | SQL Sundays #7

Key Takeaways

and we're back with another sequel sunday let's jump into it okay so this question that we have here today is from airbnb find the minimum average and maximum rental price for each review qualification category the review qualification category is a classification applied to each rental property based on the number of reviews the property has okay so it's defined below like this cool okay let's look at the table here so okay airbnb search details this is a big table uh lots of things going on let's see [Music] so we want to look at the review qualification so we're looking at the number of reviews the property has so we're basing these um classification on that so let's see if one of these columns gives us a hint about the number of views okay okay bathroom blah blah blah blah oh okay cool so number of views is literally here this is really nice i actually thought that it would be more complex since you know maybe it would be like each review and you had to aggregate it but it has a number of views here for us that's great so let us have a plan and see what we're gonna do so finally we want our expected output to look like so reviews qualification oops slice spelling area there reviews qualification um let's comment this out main price average price and max price max price cool all right so what assumptions do i have okay so first assumption is that all the values are going to be an integer so i'm going to assume that we don't have any null values here um yeah so i'm going to assume that it's gonna be positive integers for this what other things do i have i think that's about it cool all right we can go on to right what we're gonna do so first thing we're gonna do is do the classification and to do this we're going to use case 1 statements should be pretty straightforward and then after we do that i am going to um just do aggregation so group by the rental price oops not rental price um group by qualification category and then aggregate min average and maximum price cool all right let's do this query so from airbnb search details a case when what is it called again number of reviews number of reviews is equal to zero then no one number of reviews is oops nice between one and five then few when number of reviews between 6 and 15 then [Music] [Applause] number of reviews between 16 and 40 than many when number of reviews actually i can just do this else a lot because we're assuming since um it's not going to be null and it's going to be positive so this should catch everything else that's more than 40 reviews okay so end as what is it called again oh yeah reviews qualification cool um so now we can do like a sub query for this uh like nested but i always prefer to use ct's because i just think it's a lot clearer for the interviewer to read and also for you to not get confused oh oops one thing one thing one thing uh we also need a price right because we are aggregating by price cool all right so let's call this with um [Music] class with reviews as here and we can do from reviews now we're going to group by qualification category so let's write the select statement first select reviews qualification um what is it minimum so minimum of price as bin price as price as average price and max price x max price as uh max price cool and then we're gonna group by one let's see let's see looks good to me fingers crossed ran our code oh no what happened oh oh my god i forgot to write the select statement whoops okay rookie mistake okay this should work now yay okay good we got this right all right is there any other way that we can optimize things you could use a sub query like just nest it and not save it as a table so that would optimize actually no i don't would that even optimize things um that's a good point let's check that out so let's see like ct versus sub query performance okay so they're actually the same since both provide the same information to query optimizer use more than once could be easily identifying calculators out could then be stored okay so they're actually the same times performance so i don't think we can actually optimize it based upon that and i don't think that we can optimize it in any other way either okay so they did us a sub query here um yeah it's pretty much the same solution it's just the subquery i generally just prefer using cts as opposed to subqueries we're done with this episode and feel free to check out strata scratch i will link it below as well alright see you guys next time suddenly you're nowhere to be found i turn around and everything has changed looking for a way to work it out i'm trying to find some peace [Music] is [Music] again [Music] everything we wanted turned to gold the past we chose the future on our side [Music] and the writing on the [Music] can you hear me we'll meet again [Music] dark for the sunrise clouds for a blue sky space for the traveling star [Music] strong from the inside you're still my lifeline i feel you wherever you the oak are where i met you and the writing on the statue i still remember happy words [Music] follow me again you