Real Data Science SQL Interview Questions and Answers # 2 | Data Science Interview Questions

Key Takeaways

hey everyone welcome back to the channel today we'll be going through another sql mock interview question just like they do it in the fang interviews so you guys seem to have liked it a lot last time where i went through a real interview question and then went through step by step how i approached it how i actually wrote the query and finally how i checked my answers using some dummy data i made up on db sql so today we'll be doing another question just like that all right let's get started today's question is write a sql query to find out the overall friend acceptance rate for each date the table was given to us and that is friend request ds which is date stamp sender receiver and action and action can be sent accepted rejected etc okay cool so just reading the question another time we want the overall friend acceptance rate for each day so our final table should look something like ds so date spam for each day and then the overall so we can call that friend acceptance rate oops and this is essentially going to be accepted divided by total and might as well make that percentage so times 100 okay so let's see i have some assumptions for this table um so my assumption is that the action here is sent accepted rejected i'm going to assume that these actions like if a sender sends it over to the receiver and they didn't respond to it that would actually be counted as sent and then other actions can be accepted and rejected and this is important because when we're doing our total calculation over here that means that the total amount is going to be all the actions combined together okay oh and also um another assumption i'm gonna make is that we're gonna only have unique rows over here so if a sender would then send something to the receiver and you know send it multiple times that wouldn't count okay so let's get started writing our query oops from friend requests so we're grouping by ds and then our next step is accepted over count of options times 100 so we have our select statement we're going to be selecting oops what am i doing group by one select yes yeah so we're grouping by date stamp and then here i'm gonna use a case when statement so case when action is equal to accepted then one else no oops let's just split this up so it's easier to see else know and i'll go over in a bit um about exactly what that means so i want to count that and that should give us the number of people that are accepted i'll also go over why i'm multiplying by 1.00 um and then we're going to divide that by total count option multiply that by a hundred and that should equal the percentage acceptance okay so i'm going to go over this step by step so grouping by one which is the date stamp over here and this is when i'm using a case when statement so here i'm saying that when the case one action is equal to accepted then we're going to count that as the number one else is going to be null and that is our statement and we are counting that so that it's basically counting only the times when the action is equal to accepted and then other times we're not going to count that at all and it's actually really important here for us to write else null not like else zero because the way that sql works when you're counting something it will count just the number um of rows that you have over here but if you have null it wouldn't count it so if you actually put else zero it would count zero as well because it's just simply counting the number of rows and i'm multiplying by 1.00 here and that's because there's something called integer division so when you're dividing an integer by another integer the default is that your number that you get is going to be a whole number so they're just going to round um i think it either rounds up or rounds down i'm not sure but if you basically don't get an actual fraction and we want the fraction of it so i'm multiplying it by a float and that will change it to a float so when we're doing the division over here we'll actually get a fraction instead um so yeah we're going to divide that by the count action which is all the actions that are there which is our total and then we're multiplying that by 100 in order to get a percentage and that is our percentage acceptance that looks right to me yeah that looks that looks pretty good to me um so i don't really see anything else over here uh in terms of optimizing i also don't see too much optimization you know i don't really see any optimization um do let me know in the comments though if you guys have a different approach to this or even if it could be more efficient it could be less efficient i i'll just be really interested in knowing what the way that you guys approach this question as well um there's really lots of ways of doing a query so also don't think that this is you know the way that you have to do it no it's it's definitely not um so yeah i think in terms of this query i am pretty good to go uh i do want to point out though that the assumption that we made earlier that if a sender sends me to a receiver on the same date and they do that multiple times it would actually only count as one row and if we ever wanted um because we made that assumption over here if we ever wanted to only count that as one time we might have to do a distinct statement in order to do that but since we stated upfront that's what our assumption is so we should be okay i just put in some dummy data that i created earlier um and i'll walk through it with you guys as well so we have our table friend request and you have date stamp which is a date sender which is an int receiver which is also in an action which is a var chart and we're going to insert into this friend request table ds sender receiver in action and the values that we had our current day so in postgres current day is a shorthand for today so we have current day sender is one receiver is two and action is sent then i have another one from one to three today that is accepted and then we have current day minus one so that's going to be yesterday and we have one to five rejected another one from yesterday one two three accepted and we're gonna have another one current day minus two from two days ago and that's two to three and that's accepted so i created quite a few rows of dummy data because i'm usually pretty paranoid just in case i like forgot some edge cases where this doesn't work out so i tend to like create more scenarios and see if it works out or not all right let us put in our query cross our fingers that are works please work and success okay cool let's go over this a little bit and just to make sure that it actually works okay so september 9th today is the eleven so that's from two days ago so it's the last line over here and we have accepted so that looks right to me we have 100 acceptance rate and then for september 11th which is today we should have let's see 50 and we do have 50 and finally for the 10th over here rejected accepted we should also have 50 which we do over here as well so this looks pretty good to me yeah seems like we did it oh actually one thing um it's not that big of a deal but you know percent acceptance here there's just like a lot of zeros afterwards if you ever want to round that number up you can do something like round two and this will round it to two decimal places you can also do whatever number that you want so yeah that looks good so i hope you guys enjoyed this video of me going through another sql interview question exactly the way that i did it for my fang interview and this is also the way that i studied for my fang interview where i went through everything step by step and also talk through the entire query i prefer to do it this way um because when you're with an interviewer first of all it's a lot less awkward and second of all if you make a mistake in your logic or when you're writing a query even it's like a small syntax error the interviewer can actually know that you're making that mistake and correct you so you don't you know accidentally go off on the wrong track so i think that's really really helpful since you guys also really enjoyed this style video these sql mock interview question walkthroughs i've also decided to make this into a series so i'll be posting one video a week in this exact style where i'll be going over a sql interview question if you're currently interviewing or even just planning to interview in the future for data science positions i highly recommend that you follow this series and not just watch me do the questions but actually do the questions yourself as well and just follow along and since we're going to be doing this one time every week i think by the time that you're actually going to be interviewing for real you guys are going to do really really well on the interviews and finally shout out to andrew from data leap he's been doing some really cool stuff on his channel and in particularly relevant to this video he's doing sequel interview questions as well and he's doing them live so if you guys are looking for more videos for sql practice head over to his channel and check that out i'll be linking it above as well that's it for this video and thank you so much for sticking to the very end i'll see you in the next video