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

Key Takeaways

hey everyone welcome back to another video so some of you have reached out to me in the past couple weeks or so asking me to make more sql videos specifically uh walkthroughs of sql interview questions so your wish is my command and here it is by the way if you watch my past video about how i learned sql from scratch in 11 days to pass my fang data science interview you would know that i have an obsession with my whiteboard i prefer to do everything on my whiteboard however you guys did make a really good point because interviews are going to be virtual now so they're probably going to also um that final whiteboarding round is going to be virtual as well and they're going to use something called coderpad usually which is just like a text editor similar to sublime text so i'm gonna just show you guys using sublime because i think coderpad is paid and it's pretty much the same thing anyway all right so let's get started the question that we're gonna be looking at today is a sql interview question asked by a tech company the question is write a sql query to count the number of unique users per day who logged in from both iphone and web where iphone logs and web logs are in distinct relations so this question doesn't tell us um what the iphone logs and web logs actually looks like so we're going to have to make some assumptions here all right so let's see for iphone logs let's call that iphone um i'm gonna assume that it's gonna be time stamps called ts user id and iphone session id and similarly for web blog it's going to be timestamp user id and web session id okay so i'm going to just assume that this is what our relations are going to be looking like and now i'm going to write down each step of how to tackle this query so the first step is going to be join so we want to join together the iphone and the web tables uh and then after that we're gonna be matching by day and user id and finally we're going to be grouping by the day and we want to count the number of users per day so our final table is probably going to look something like uh day num users all right so we now know what it is that we want it to look like all right so time to write the query from iphone i join web and we're going to be joining on here match by day and user id so ida user id is equal to w dot user id and so we're going to be joining by the day but time stamp our assumption is that um it actually includes both the day and that time associated with that but since we only care about the date and we don't actually care about the granularity of the hours where the minutes or the seconds so we can actually truncate that so the function here is date trunk uh day and i dot user oops not user id i dot ts is equal to date trunk day w dot ts so that should match by day all right so our third step here is to group by the day and count the number of users okay so let's write the select statement first we want the day over here so we're gonna do date trunk again day i dot yes as day and then we're going to count the number of users so count so the number of users we want are unique so we want to count distinct oops uh i dot user id as num users and um i just wanted to make a point here in saying that we used an inner join because we didn't specify what kind of join that we're using so that should only join together users that were logged in from both iphone and web all right so we need to write the group by clause so group by one all right let us look this over one more time select date trunk okay so that should give us the day and it should give us the count as the number of users iphone i web here joining is correct okay so that looks correct to me and now i'm going to think about if there's any ways of making it more optimized okay so i think that this is pretty much as optimal that you can get um like we need to distinct here in case like people log in on their iphone and web multiple times throughout the day we only can't want to count the number of users that are there so we do need that and in terms of date trunk i mean there could be a function that's more efficient than that but i think this is fine okay yeah this looks good to me so our final step now is to actually test out this query and see if we made any mistakes before we get into that i just wanted to say that if you're enjoying this video and finding it useful consider liking the video and even subscribing to the channel um i'm planning to make more videos like these depending on you know how much you guys like them um but yeah like that's your interactions with me and your engagements is how i engage how useful you guys are finding my material and it really motivates me to make more videos like these all right so this is sql fiddle and i just copy pasted some dummy data that i made previously uh just like for the sake of time you guys don't have to watch me trying to come up with data to play in um but i'll still go over it with you guys though so i created two tables one called webco one called iphone which is exactly the tables that we assume that we were having when you're writing the query so it has timestamp user id web session id and for iphone has timestamp user id and iphone session id and i also just inserted some values in to test that out query with so here we have in web um now so like timestamp is going to be currently user id is 1 and session id is 100 and then this is a from one hour ago it's still the same user and the session is 101 and then i did another one that was from one day ago user id is two to a separate person and the session id is 103. for iphone i did something similar so our same user here user number one and then from two hours ago user number one again i did user one and i did duplicates of this just to make sure that we actually have unique number of users um sorry just to make sure that we have unique users because we don't want duplicates to show up um and then we have from one day ago but this one is different the user id is three so after we run our query the return should be we only have one row for today and we should have number of users is going to be one because the only one that should match is user id number one and only one of them alright so let us copy paste our actual query oops that is not our query this is our query so this is the query that we just wrote um hopefully it works fingers crossed no so many errors oh okay so this is my sql server so i actually wrote this in postgres sql so let's change that and try again yay it worked success all right so this is what we expected um we have today which is september 2nd and number of users is one so depending on how paranoid i am and just kind of like how confident i am in my query sometimes i would add in more dummy data to see if i can like catch any edge cases that my query doesn't wouldn't be able to process but in this case i'm pretty confident that this query will work in all edge cases so yeah i think i'm just going to leave it as that so that was the final step and i just wanted to stress that it's really important to actually test out your queries because oftentimes you know you think that's crap but maybe there's like small syntax error which is i things that you didn't catch previously and this feedback is really important for you actually learning so next time when something like that comes up again you wouldn't make the same mistake twice and that's it for this question this is what i did for all the sql questions i practiced when i was prepping for my own interview at the fane company and um yeah let me know what you guys think about this video i've never done like live coding like this before um so you know let me know if it's like too slow too fast and being like really confusing you know maybe i'm like repeating myself repeatedly something like that right um and if you guys enjoy this video also let me know if you want me to do more like this um maybe it's helpful to see me just like go through more interview questions maybe not just let me know because i know what kind of videos to make in the future as well all right so thank you for watching this video and i'll see you guys next time