Practice SQL Interview Query | Big 4 Interview Question

hey guys tawfiq here now in this video i will be solving one more real sql interview query now this was a question that was asked by one of the big four accounting firms and it was shared to me by one of my subscribers in my email now i believe this is a very common kind of a question that can be asked during sql interviews uh maybe not the exact question but different variations to similar problem can be asked so definitely check this video until the end okay now straight away let's get into this problem okay so as you can see we have been given a table with three different columns this is having some information about different brands so there are certain records with apple certain records with brand samsung and there are certain records for the brand nokia okay there is a column for year and there is a column for amount okay now the problem statement basically tells us that we need to write a query to fetch the record of brand whose amount is increasing every year okay now let's first try to understand what is expected so basically they are telling us that we need to write a query which is going to display only those brands whose amount has increased every year meaning that if i look at the first record here for apple in 2018 its amount was 45 000 okay in 2019 its amount reduced to 35 000 so its amount has decreased from 2018 to 2019 by few thousand right so basically this is not the record or this is not the brand that we need to consider for our output okay so because the amount has decreased from one year to another okay when i come to the brand samsung i can see in 2018 samsung had 15 000 in 2019 it increased to 20 000 and in 2020 its amount increased to 25 000 so looks like samsung is a brand in this table where its amount has increased every single year and when i come to nokia i can see that in 2018 the amount was 21 000 2019 it decreased to 17 000 and then again in 2020 also it decreased to 14 000. so this brand here nokia its amount has decreased right so out of these three brands if i have to tell that which is the brand whose amount has increased every single year then it's only this particular brand that is samsung so we somehow need to write a query which is going to only return a samsung okay and the query should not only work for the data that is given here in general the query should work for even if this data grip now in order to solve this problem i have already created the same table in my database i'm using postgresql okay and this is the pg admin tool as you can see here i have created the brand stable it is having the same data here okay now before i can solve this problem let's try to understand whenever you have this kind of a problem what can be your approach to solve similar problem because i'm not making this video just so that i can share with you the solution but the intention is that i can share with you the approach and the thinking that you need to have whenever you come across similar kind of problems okay now what we need to do is we need to write a query which is going to process every record one at a time okay and when it is processing one record it should be able to find the amount from the next record okay and then it needs to do a comparison if the amount in the current record and if the amount in the next record it's basically increasing then we can tell that yeah this is a successful record right we need to have that kind of an approach okay now first of all whenever we have this kind of requirement where we are processing a record but we want to fetch a value from another record the best function that we can use to fetch the value from the next record is the lead window function right using the lead window function even when we are processing the first record we will be able to access any column value from any of the next record right so looks like lead window function can be one of the approach or one of the possible solutions for this particular problem okay that's fine but there is one more thing that we need to consider and that is when we are processing each of this record and when we are looking for the next record we should not compare between the records if the record belongs to different brands so when our query is processing the last record for this brand apple it should basically not process the next record which might belong to some other brand right so meaning that we need to process records which belongs to the same brand separately so in other words we kind of need to create a partition for each brand so one partition for apple another partition for samsung and another partition for nokia okay and inside that partition we can use the lead function to compare the current record with the next record so this can be an approach but even after doing that we will not be able to find the final output okay so for example let's say as part of our first approach that is by using the lead window function we'll just be able to compare the current record with the next record and if it is successful then we can put a flag like one and if it is failure we can put a flag like zero right so for example let's say i'm going to create a column like flag here okay and then i'm going to compare this record that is 45000 with this 35000 using the lead function and here i can say that 45000 is less than 35000 so this one i can make it like zero okay when i process the second record and i compare it with the third record i can tell that 35 is less than 75000 so this can be one okay and when i come to the last record for this particular brand apple since there is no next record to compare it with by default it should display like one okay something like this okay and the same thing i will do for every other record so samsung here 15 is greater than 20 000 so i'll tell one 20 is greater than 25 000 again one and 25 000 is the last record in that partition there is no no other record within samsung to get compared with hence this will by default b1 okay when it comes to nokia 21 is less than 17 so this will be 0 17 is less than 14 this will again be 0 and 14 since it's the last record by default i'll put it like one so in my very first step i need to write a query which is going to return me a flag for every record which will look something like this okay so once i have got this flag record then i can filter the data such that i only fetch those brands who's having only one as the flag so this is the approach that we are going to follow and i hope this is clear now let's get back into pg admin to write this query okay now before i can continue i'd 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 concept that you can think of now personally i'm very much interested in learning about how to make better youtube videos how to become more productive how to better manage my finances and currently i'm also interested in how to get better at photography now instead of going online and trying to learn all of these skills through different resources i could just go into skillshare and find some fantastic classes to learn all of these skills in one single platform for example the youtube success class conducted by mkbhd on skillshare is definitely one of the best classes that you can find in order to learn about how to make better youtube videos it has definitely helped me i have learned a lot of things about how to make better youtube videos the lighting setup how to edit videos etc and when it comes to productivity the mastering productivity class conducted by thomas frank is definitely one of the best productivity classes that you can find online so if you are someone who wants to learn new skills and you want to implement these new skills in your life then skillshare is a platform that you should definitely check out now the best part is you can join skillshare for free you can use the link that i will be leaving in the description below to join skillshare for free and you can get one month of free access to all the online classes that are available on skillshare so definitely check the link in the description below it's free definitely utilize it learn new skills and i hope it helps thank you skillshare for sponsoring this video now let's get back into our content so my very first step is to basically kind of create this flag column right using the lead window function so what i'm going to do is i'm just going to move this to the right and here for now let me first create the lead window function so i'm just going to say lead what i need is when i'm processing this record i need to fetch the amount from the next record right lead will accept a few arguments uh the first argument will be the value that it is going to display so i'm just going to say amount okay and then i'm going to say over and inside my over clause since this lead is a window function over clause is mandatory i need to use it here first of all i need to do the partition right i need to do the partition based on each brand so i can just tell partition by brand okay and then when i'm processing this record i should make sure that the records are sorted in the form of year right so for that i can just tell order by year and by default it will be ascending order that is exactly what i want okay and let's say i am just going to run this okay so a new column has been created i don't actually need this column i just need to compare the amount from the very next record with the amount that is present in my current record right so i don't really need this column but i just need to do the comparison hence instead of displaying the value that is returned from lead i'll just use the case statement to do the comparison of amount from the current row with the amount that i'm going to get from my lead function right so i can just tell case when amount this amount is the amount of my current row okay from here okay and i can just tell if it is less than the lead among lead means the amount from the next record because by default lead will look for one record next to the current record okay i can of course make it to look for many records after the current record but by default it will be one okay so i'll just leave it as it is and if the amount is greater than the amount present in the next record then i want it to be displayed like one else it should display like zero and i'm going to end my case statement and let's say i'm going to call it like the column flag okay so maybe just to make it more clear i'll just move this then clause in the next line i'll put the else part here and i'll end my case statement here okay and maybe i'll just put this entire case statement into the parenthesis just to make it clear and now i'll run it and now you can see that i have got this flag column so for the first record it is telling 0 because here it is less than basically it's greater than the next record and i'm getting one here and zero here and i think for samsung this is the only okay there is still one problem here that is you can see here the last record of samsung it is telling the flag as zero similarly the last record of apple it is telling like zero similarly the last record of nokia also it is telling like zero okay now this is not exactly what i want as i told here uh if the lead function does not have the next record to compare it with by default i want it to display like one right so how can i do that so so imagine when sql is processing this last record okay using the lead function uh it it's going to compare this 25000 so this amount is 25 000 and this lead of amount is going to look for the amount from the next record but there is no next record so by default it's going to return null so this statement is going to return false hence it's going to return 0 here but i do want it to return 0 since there is no next record by default i want it to return 1 so what i can do is there are two more arguments for the lead function so the second argument is the offset value that is how many records next i want to compare it with by default it is one and here also i want to compare with the very next record only right so i'm just going to keep it like one and here the third argument is basically the default value that gets returned if there is no next record okay here what i want to do is if there is no next record then i want it to always return one okay how can i make this statement to always return one is i can just copy this amount here but if i leave it here amount less than amount will still return false right but i want i i kind of want to force it to return true right if there is no next record so what i'm going to do is i'm just going to say plus 1 so it's going to compare if amount is less than amount plus one this will always be greater than amount right hence when there is no last record the default value that is going to return is this one amount plus one okay that is this lead function is going to return m1 plus one and this comparison expression is going to return true and the final output here will be 1 okay now if i run this now you can see here all the last record here it is returning like 1 even for the nokia the last record is 1 and for apple the last record is one okay i hope this is clear now we have basically done most of the things but this still is not our final output right our final output is from this result set we only want to extract the brand whose amount has increased every year in other words whose flag is always one right so what i can do is i can't put this into a sub query but a more neater way is to just put this inside a city table so i'm just going to use the width clause with cts and i'll just move this to the right and here i'll just put the parentheses that's fine and here what i'm just going to do is i'm just going to say select start from brands okay this brands is actually my input table the table which holds my input data okay so from my brand table so let's say if i only execute select start from brands i will get all the data from my input table that is fine and here the subquery that have created the ct it's going to have this additional flag right what i want to do is i want to only fetch the data where brands is not in i'll just tell select brand from cte where flag equal to zero okay so what i'm doing is from this cte table i'm going to fetch all the brands who is having a flag equal to zero so it's going to fetch apple and it's going to fit nokia okay but it will not fetch samsung why because in any of the records that belong to samsung it does not have a flag 0 right so it's only going to return apple and nokia right and my final output i'm excluding all those records which belongs to the brand that is returned from this sub query here okay so hopefully this should only return me samsung and now if i run this i'm getting an error so let's try to fix that i think the column name is brand here and now let me run this and now you can see that i am getting the final output so this is the correct output that is samsung is the only brand whose amount increased every year okay and this is one of the ways i have solved this problem i hope this is clear and you liked this kind of videos where i am showing you the approach and breaking the query into multiple different parts and then writing the query to solve it okay now all the scripts sql queries and data set everything is in my blog the link to my blog is present in the video description if you like this video give me a thumbs up and if you have any other sql interview queries or any other interesting queries that you would like me to make video about definitely email me your query my email id is also present in the video description thank you so much for watching and see you soon in the next one bye

Channel: techTFQ

Views: 70,560

Keywords: sql, sql queries, practice sql queries, sql interview queries, sql interview question, sql problem, interview question, interview sql query, data analyst sql interview, big 4 sql interview query, big 4 interview question, techtfq, techtfq sql, sql interview questions, sql interview questions and answers, interview questions sql, sql query, sql tutorial, sql query writing, complex sql queries, sql interview, learn sql, solving sql queries, sql query tutorial, data analyst

Id: dWHSt0BVlv0

Length: 14min 47sec (887 seconds)

Published: Mon Sep 12 2022