SQL Data Science Interview Questions and Answers (interview style walk-through) | SQL Sundays #4

hey guys you know the drill let's jump into the question all right so today's question is from yelp and it's find the number not the number find the business and the review text that receives the highest number of cool votes okay so find the business and the review text that receive the highest number of cool votes we want to output the business name along with the review text cool so let's take a look at the table right so it's called yelp reviews and we have business name review id user id stars review date review text funny useful and cool so we what we want is the business name along with the review text all right so let's also take a look at the expected output okay so it's literally just the business name and the review text i assume it's the one with the most amount of cool votes okay so straight up what assumptions do we have okay so the assumption that i have right now is if we're ordering it by the number of cool votes there might be more than one person or um more like more than one review i guess that has the same number of cool votes so you might have like the top three people all have the same number of cool votes so in that case i'm gonna truncate it so we only get the first one so that's the assumption i'm going to make here all right so let's jump into the question how would i approach this okay so we are finding so what we're going to do is we're gonna order by cool and we want to select the business and review text and we only want the coolest one so limit by one all right so let's write your query from yelp reviews select business name and the review text we want to order by cool descending here and we want to limit to one double checking that looks right to me fingers crossed let us try it out yay we did it before we get excited let's actually see if it's correct okay that looks right to me so why don't we check out their solution as well see i told you guys there's so many ways of approaching the same question they did it really different from how i did it so how did they do this okay so they used an inner join interesting so they went from yelp reviews and they found okay so what they did is they found the coolest one and then interjoined it with um the table again so that you would get the review with the max coolness and the business name review text totally works as well so i actually think though that my solution here is more efficient because i don't do an inner join and i only filter at one time so that saves us one filter and one joint all right so in terms of anything i can do to make this more efficient slash any other business aspect i can consider so i think that this is pretty efficient to me do let me know in the comments below how you guys approach this question i'm always interested in seeing your solutions as well um so in terms of business-wise i feel like this is pretty self-evident you want to find the business name and review text that is the most cool on the up reviews so that looks good to me well i think that's about it for this video oh actually by the way if you guys haven't checked out strata scratch yet which is the platform that i'm using now for these weekly sql interview questions do check that out i think it's actually really helpful and really has saved me so much time i'll link it below as well so yeah if you're interested do check that out and i'll see you guys in the next video