SQL Interview Problem asked by Product Based Company | Solving SQL Interview Query

hey everyone welcome back now in this video let's try to solve one more real SQL interview problem now this problem was asked by a product based company it was shared to me by one of my subscribers called tarun and he had emailed me this problem now if you are beginner in SQL this problem might seem a little complicated but actually this is a very simple problem with a very simple solution so let's straight away get into the problem and try to solve it okay so as you can see the problem statement is actually pretty straightforward we have been given a input table and we are expected to write a query which is going to return the output as shown here now when we look at the input we can see that we have two columns so there is an ID and there is a name so this is some information about some employees so we can see we have eight different records so there is id1 and employee one and same way for all the other eight employees okay now what we need to do is we need to kind of come up with an output like this okay so there are two things that is happening here the first thing is you see here we have one space employee one then comma two space employee two so what this means is first of all they are actually trying to concatenate ID and name together with a space in between that's one thing the second thing is they are merging the two records together and putting them into a single column right so employee one and employee 2 are merged together and displayed as one row right under one column and then employee three and four are most together and displayed as one column and then employee 5 and 6 are merged together and displayed here and finally employee 7 and 8 are also merged together and displayed here now whenever I look at a problem the first thing that I try to do is once I have understood the problem statement the requirement and the input data I try to figure out what concept of SQL can I apply to come up with a output as expected in this particular case even before I can come up with a solution I need to understand what exactly are the different things that I need to do and here I can see that there are two different things that I need to do in my query the first thing that I can pretty easily tell is that I need to somehow find a way to convert row level data into a column level data and not into multiple columns but into a single columns and commerce operated values right so that is one thing that I need to do and when I think I can easily do this kind of transformation that is multiple rows into a single column by using inbuilt functions that are almost available in all rdbms okay now this particular problem was asked to be solved using my Microsoft SQL server but I will be solving it here using postcode SQL but the thing is I'm going to be giving you solution in all the rdbms okay so let's say if I'm using postcode SQL or let's say in Microsoft SQL Server what is that function that I can use to convert a row level data into a single column with comma separated value maybe you guys can think okay and actually the answer is we have a function called as string aggregate right string underscore AGG okay we can use that function to basically merge multiple rows into a single column in commas operated values okay so I think we can easily do that transformation using string aggregate function okay that's one thing the second thing that we need to do is when we are doing this aggregation or kind of like merging of multiple rows into a single column we are actually not merging all the eight rows into a single column right we are first of all dividing these eight rows into kind of like four different sections right the first two employees will form the first row in the result the next two employees that is employee 3 and 4 will form the second row and then 5 and 6 gets merged together and then 7 and 8 gets merged together okay now in SQL how do we do this right that is we need to kind of put the first two employees into one bucket then the three and four employee into the second bucket then I create a third bucket where I put the employees five and six and then I create another bucket where I put the employees 7 and 8. so in other words I need to kind of create four buckets and in each bucket I need to put two employees can you think of any function or functionality in SQL with which we can easily do this and actually the answer is we have a window function called as Yen tile with which we can pretty easily do this right so let's try to solve this problem using the entire window function and the string aggregate inbuilt function okay so let's go back to my postgresql I am going to be solving it in Project SQL but the solution will also work in Microsoft SQL server and for other rdbms I'm going to be giving you solution in my blog okay so definitely check out the blog for the data just had the solutions and everything okay let's try to solve this problem you can see that I have created the employee underscore input table as you can see here now the first thing that I'm going to be doing is I'm going to create four buckets and under each bucket I'm going to put two employees each okay now in order to create this bucket I'm going to be using the window function so what I'm just going to do is I'm just going to say end tile okay and this Intel window function will accept a argument that argument is you need to tell how many buckets SQL need to create right so here I know that I need to create four buckets I'm just going to put four okay so what this entire function will do is now first of all if you are not clear about entire function or any other window function I have made dedicated tutorial video on all the window functions so I'll try to leave the link somewhere here or in the video description so definitely check that out if you need to understand how Intel actually works okay but in the most simplest of terms what entire does is depending on how many records are present in your result set that is from this particular query how many records will get returned it will try to equally split the number of Records based on how many buckets you have mentioned here under each bucket it will try to allocate equal number of records as much as possible okay so here when I say n Type 4 it's going to create four buckets and under each bucket it's going to allocate two records now since it's a window function I also need to use the Over clause and here I'm just going to say order by ID column and let's say I'm going to name this column like let's say buckets okay if I run this now you can see that I have created four buckets okay so there is bucket one bucket two bucket 3 and bucket four and you can see that all the eight records are equally distributed into each of these four buckets so now we have basically done the first part that is we have created four groups or four buckets with two employees each right the next thing is we kind of need to merge together each employees which belong to the same bucket now to do that I'm going to be using the string aggregate inbuilt function okay so what I will do is I'll try to put this entire query into a city so I'm just going to say cdes and I'll move this to the right and here I'll just put this into the parentheses okay and here I'll write my outer query so select star from CTE and since I need to merge the employees or the records which belong to the same buckets into a single record what I do is I'll try to group the data based on each of these buckets so for each unique bucket one group will be created and within that group you will see that there will be two records and these two records I'll try to merge together using the string aggregate function so first of all I need to do a group by so I'm just going to say Group by I'll say buckets okay that's the first thing and here I need to merge the employees right but first of all you can see here I have two different columns ID and name but if I go back to my output you can see that I only have one column right so the ID and name are actually concatenated together and there is a space in between so first of all let's try to do that here instead of displaying star I'm just going to say concat and here I'll say ID and I'll leave a space okay and here I'll say name okay and this whole thing let's say I'm just going to call it like okay and now let's say I'm just going to run this okay excluding the group by and now if I run you can see that now the ID and name are merged together okay this is exactly how I want it in my output right so if I go back here now the next thing that I need to do is I need to use the string aggregate function so I'm just going to say string underscore aggregate okay it will accept two arguments the first argument is the column itself and the second argument is what is a separator right I'm just going to say you need to separate each of those values using a comma and a space and I'm going to name this entire thing as let's say final result before I run this entire query let me just run only this much okay so that you see what happens if I did not use Group by so if I run this now now you can see that since I did not group the data based on each unique bucket the entire eight records were merged together into a single column and there is a comma and a space in between each of these values as you can see here okay but what I want in my output is I want only the first two employees in one record then the next two employees in the next record Etc and that I can determine using my bucket and that is why I'm going to do a group by buckets and run this entire thing again okay so now when I run this now you can see that this is the exact output I want maybe the order is not properly sorted so I'm just going to say order by one okay or this particular column so if I run this now you can see that I am getting the output exactly how I want it so employee one and two three and four five and six and seven and eight exactly what I wanted I hope you like this video if you did give me a thumbs up and subscribe to the channel and if you have other solutions to the same problem which I'm sure many of you guys already have definitely share your solution in the comments below thank you so much for watching and see you soon in the next one bye

Channel: techTFQ

Views: 62,839

Keywords: sql, sql interview, sql problem, sql interview problem, sql interview query, sql interview by product based company, product based company interview query, sql query, solving sql query, sql interview questions and answers, sql interview questions, sql query writing, sql interview question, sql queries, practice sql queries, sql query tutorial, techtfq sql, sql tutorial, solving sql queries

Id: 90iK6gGvG_g

Length: 9min 22sec (562 seconds)

Published: Wed Oct 05 2022