Google SQL Interview Problem | Solving SQL Interview Query

this video is sponsored by skillshare hey everyone welcome back now in this video let's try to solve one more real SQL interview problem now this time the problem was asked by Google it was shared to me by Abdul in my email and he faced this problem I think when he gave an interview for Google so this problem itself is actually pretty simple and the solution is also pretty simple but of course there is slight amount of trickiness that you might need to consider when solving this kind of a problem so straight away let's look at this problem statement and try to solve it okay so let's look at the problem statement as given by Abdul in his email we'll try to understand this and then try to solve it okay so he says that there is a given table that is user activity this is a structure of the table and they say that this table does not contain any primary key this table contains information about activity performed by each user in a period of time okay a person with a user name performed an activity from start date to end date okay and they are asking us to write the SQL query to show the second most recent activity of each user okay so if a user only has one activity then return that record itself a user can't perform more than one activity at the same time return the result table in any order okay so they are telling that we have been given a input table user activity the data looks something like this so we have three records for Amy and there is one record for Joe and Amy has done the activity travel between the date 12th February 2020 and 20th February and then from 21st February to 23rd February Amy did a dancing and then from 24th February to 28 February Amy did travel okay and what we need to do is we need to find the second most recent activity the second most recent so if I had to see the most recent activity then it would be this one right the last activity that she did that is a recent activity but we need to find a second most recent that second most would be this one right the second record here dancing right now this is for Amy but when it comes to Joe since he has only one record it is mentioned that if the user only has one activity then return that so Joe has done really one activity so there is no second most recent activity so we need to return the record that is the only record that is present okay so and here they are mentioning the required output that is Amy Dancing and Joe a travel I hope this is clear now the given input data is only for four records but I have created four additional records just so that we can write a solution which would work for all different scenarios so as you can see I have created the table in my database so this is a four record that was present in the problem statement I created these four records with a new user Adam okay and I have some four different activities that he has performed during some days now let's try to write a query which is going to transform this input into something like this so that we can fetch it the second most recent activity for each user now let's think of a possible approach that we can use to solve this problem so what I need to do is from this table data I need to fetch this record that is okay yeah this is the second most recent record and then I need to fetch this record so this record this record and then from Adam the second most recent is this one right so you can see that all the records are sorted in the form of start date okay so the second most recent activity for Adam is this one right now how do we find these records and fetch only these records so what I can do is so first of all I can do one thing I can try to partition the data or group the data based on each user name okay so for one partition for any one for Joe and one for other and within that partition first of all I can count how many records are present okay so I can say that in for Amy there are three records for for Joe there is one record for Adam there are four records so I can use the count function to count the number of records and then what I can do is I can I can use the row number to give a unique row number for each of this record and then what I can do finally is using this row number and using the count I can only fetch the second most recent record so for example for these four records from Adam what I can say is if I count the number of records for other I would get the count as four and if I give a unique row number for each of this record I would have one two three and four I know that account is going to return me the total number of Records right so if I want the second most recent activity if I do count minus one that will be three and if I fetch the record with the row number three then hopefully I will get this record and the same thing will apply for all the other uh Records right but for for the person Joe since he has only one record I can put some case statement to handle that I hope you kind of get an idea how we can solve it using the row number and the count functions so let's straight away try to write the solution for this now before I can continue I would like to thank the sponsor of today's video which is skillshare now skillshare is an online learning community where you can find thousands of online classes related to almost any skill that you can think of now one of my favorite classes that I have taken on skillshare is the YouTube success class conducted by MKBHD now MKBHD or Marcus Brownlee is one of the most reputed creators on YouTube and his class on skillshare can help anyone who is interested in making good YouTube videos now in his class he starts by talking about how to come up with YouTube video ideas he then talks about how to script YouTube videos he then shares his knowledge on how to shoot videos and some really interesting tips on lighting setup for YouTube videos at the end he also shared some very useful tips and tricks about video editing now similar to this particular class there are thousands of other classes on skillshare that can help anyone learn any skill so if you are someone who wants to gain some new skills then skillshare is a platform that you should definitely check out now the best part about skillshare is that you can access all the online classes that they already have for free for up to one month by using my link which I'll be sharing in the description below now remember only the first 1000 people who will be clicking on my link will get one month of free access to skillshare so definitely check out a skillshare and try to gain some new skills so first of all I am going to create a row number for each of these records so I'm just going to use the window function row number so I'm just going to say row number over and I will partition the data based on each user so Partition by user name and I want to sort the data in proper order that is using the start date right so that we can fetch the second most recent activity properly right so order by start date okay and let it be in ascending order and I think I'm going to name it like row number so if I run this now you can see that I am getting the wrong number so for Adam I have one two three four for Amy I have one two three and for Joe there is only one okay this is fine what's the next step the next step is I want to find how many records are present for each of these users okay so let's first try to create discount function so I'm just going to say count of let's say star okay over and I'll use the same thing that is this one so Partition by this Partition by username and order by start date and I'm going to call it like let's say count okay if I run this now you can see that I'm getting some values for count but this count is not actually correct okay because you see here the first four records are of Adam right and the count should have been four because when I do count and when I do Partition by user name that means these four records will be formed within that partition or within that window and within that window when I run count I should be able to see that there are four records so SQL should return me four but SQL is returning some other value now the reason why SQL is not able to fetch the correct count value is because of something called as default frame Clause okay now whenever you use a window function there is a default frame Clause that SQL uses now the thing is this entire thing is my result set now within that result set I can create partitions or I can create a Windows using the window function as I have done here and within that window again SQL considers something called as a frame okay and by default there is a frame that SQL uses and whatever SQL is able to fetch within that frame SQL is going to return that value okay now if you are not sure about frame clause and everything I have created a tutorial video on window functions where I have explained this Frame clause in detail I am going to leave that video link somewhere here or in the video description so try to check that out to understand frame Clause okay here I try to give you a brief explanation about that okay now what I mean to say is so for example I'm just going to do one thing okay I'm just going to copy this I'm going to paste it here and here I'm going to add a frame Clause okay so the frame Clause can be added like this range between unbounded proceeding and current row now what you see here is called as a frame Clause this basically tells SQL that within the window only get access to whatever records will fall in this range okay and by default the records that fall in the range is this one from unbounded preceding until current row now unbounded proceeding basically means the very first record from in your window that is this particular record the first record and current row basically is generally SQL you'll process one record at a time right so when SQL is processing the first record the frame Clause will only return first record why because it starts from unburned proceeding that is very first record of your window till the current or the current row is the first record itself when SQL goes to process the second record in this case unburned proceeding will be roll from the first record in that window and current row will be the second record so it will have access to two records and that is why count of two records will return to when it goes to processing the third record unbounded proceeding will again return from the very first record of that window that is from the first line here and then current row will be the third record so there is totally three records so the count will return three and the same thing will happen for the fourth record here so this is what a frame clause or the default frame Clause does but we can change the default functionality of a frame clause and let's see how to do that so I'm going to add another count statement here by changing the frame Clause so here I will tell now one more thing is this line here and this line here is exactly the same okay because whether I mention this range between this thing or I don't mention it SQL internally will always consider the frame Clause to be like this okay so whether I mention it here or I don't mention it here it's basically the same thing okay so these two things are exactly the same okay now here in the third account that I have added I'm going to change the frame class so unbounded preceding and here I'm going to say unbounded following okay now what unbounded following will do is it is going to fetch the records or the last record from that window so this will mean that SQL will tell that the frame that you have currently access to is from the very first record of your window till the very last record of your window okay and now if I run this so let's say I'm going to name it like count new okay and if I run this now we can see here these two counts are exactly the same here the values are exactly the same but if you see in this last column you can see this is the correct count that I am getting why now when SQL is pressing the very first record it has access from unbounded proceeding very first record of the window and until the unbounded following that is the last record of the window so all these four records so in this four records when it does the count function it will return four okay so I hope this Frame Clause concept is clear and why I'm using this is because if I don't use this the default frame plus I'm not going to get the correct account I want the count to return the total number of records for each user and that is why I'm changing this default frame class okay now I'm going to remove this unwanted count and I'll only keep this correct count that I want okay if I run this now you can see that I am getting the correct row number and I'm getting the correct count now let's try to use these two to return our final output so what I'm going to do is I'm going to use put this into a bit gloss I'm going to say with CTS and I'll just move this to the right and I'll just put this into the parent this is and here I'll just say select star from CTE where and what I want to do is from this result set that I have got okay I only want to fetch This Record okay sorry not this one the second most recent is this one right Adam is singing and then I want Amy is dancing and Joe is travel right so how do I filter out only these records I'll try to filter out using this row number and the count New Field okay maybe instead of count new I'll just make it count okay and here I'll just tell row number is equal to what I want is if there is only one record for a user then its count will always be one because it is only one record right so what I'm just going to say is case when C and T is equal to 1 that means there's only one record then I want to make the row number as one so I'll fetch the record where the row number is one okay but let's say if there is more than one record then I want to fetch the second most recent record second most recent activity that is it should have the count minus one value so what I'm going to do is I'll say count minus one so what happens is for Adam count is 4 minus 1 is 3 so it is going to return this record and for me count is three count minus 1 will return two and it will fetch this particular record okay so that is what I'm trying to do here I hope it is clear and I'm going to end the case and I'm going to yeah that's on so I think that's all now if I run this now you can see that I am getting the expected output so if you see here Amy Adam Amy Dancing Joe travel if I go back to our expected output I had this Amy Dancing and Joe travel right so this is exactly what I wanted right yeah and this Adam is additional record that I created so these two columns are extra maybe I can just say username activity and start date so and end it right so okay so I think if I run this so this is exactly what I wanted I hope you enjoyed this video if you did please give me a thumbs up and subscribe to the channel and if you have any other solutions to the same problem which I am again sure that you have definitely share them in the comments below thank you so much for watching and see you soon in the next one bye

Channel: techTFQ

Views: 31,116

Keywords: sql, sql queries, sql query, google sql interview query, google sql, google sql interview, google sql interview problem, sql problem, techtfq sql, solving sql query, practice sql queries, sql interview, sql interview questions and answers, sql interview questions, sql interview question, sql tutorial, sql query writing, data science, sql interview query, sql query tutorial, sql interview questions for beginners, interview questions sql

Id: ueOUSjdAZY8

Length: 14min 22sec (862 seconds)

Published: Mon Oct 10 2022