Solving Medium SQL Interview Questions on Analyst Builder

Key Takeaways

what's going on everybody welcome back to another video today we're going to be solving medium SQL technical interview [Music] questions now if you watched the first video in this series you saw that we answered some easy squel technical interview questions now we're going to be solving medium level questions the medium questions are going to be a little bit more difficult but I will say that if you are practicing for a squel technical interview I highly recommend practicing the easy and the medium questions but let's not waste any time let let's head over my screen and take a look at our medium level questions but actually before we take a look at our two questions up here you can come over here go to the free questions go to the difficulty and go to moderate and these are all the medium level questions that you can try for free on analyst builder.com if you've not tried out analyst Builder I highly highly highly recommend it that is my data analytics learning platform that I'm extremely proud of you can take my full courses and try out these technical interview questions all in one place but let's head over to to our first question which is called Tech layoffs it says tech companies have been laying off employees after a large surge of hires in the past few years right a query to determine the percentage of employees that were laid off from each company output should include the company and the percentage to two decimal places of laid-off employees order by company name alphabetically uh and I think this is extremely accurate because uh that just happened uh I'm recording this in late late late 2023 um but I'm sure I'll release this in 2024 and I think you guys know what I'm talking about uh it's just a bad year for 2022 2023 with all the layoffs now we have apple Microsoft Google Amazon Facebook Tesla and these are the employees fired and what we're trying to do is output a percentage so we need to look at the company and then percentage of laid-off employees so if they had zero laid-off employees the percentage should be zero but let's say they had 6,000 of 181,000 we need to see what percentage of the entire company size was laid off was it 1% 2% um that's what we're trying to determine now we always can use hints and expected output if we need help or if we need the video walkthrough we can use it but I don't think we'll need it let's come over here and make some notes before we get started now before we write anything remember when you're in a technical interview whether it's for data analysis data engineering data science it doesn't matter when you are applying for these jobs writing it out correctly is important but I would say even more importantly it's about how you actually talk through the problem because that really shows your skill level if you're walking through it and you're just typing random stuff and it kind of makes sense but you're not talking out loud they may not understand that you really know what you're talking about and so you want to practice these questions know what you're talking about and while you're solving them do what I'm about to do which is I'm going to kind of talk through the steps that I need to do then I'm going to write it out that's what I recommend during actual interviews so the first thing that we need is we need to find the percentage now this is going to be a calculation so I'll write percentage calculation now how do we determine what the percentage is what we need to do is employees fired divided by the company size times 100 so it's employees fired divided by comp size time 100 that's the calculation that we need so we are going to go and do that in just a little bit but after that our output needs something so our output needs and I need to comment this out our output needs the company name and it needs the percentage so we're definitely going to need to include both of those and then lastly we need to order by the company name ASC which means ascending so a to Z so this is what we need to do now let's do one thing first let's just pull this up but we don't really need to start looking at the output or the order by just yet let's keep everything we'll keep this uh comma here let's keep everything but let's start working on our calculation so let's see if our calculation is correct it should be employees fired divided by the company size I'm going to put this all in parentheses um just to make sure we're doing pemos correctly and let's multiply it times 100 so let's run this and here we go now this looks correct just glancing at this because here we have 0% CU Apple didn't lay anyone off uh 3% 6,000 of 1800 that also looks correct this one I think is the most um straightforward one 15,000 into four uh 40,000 that should be around 10% but because it's 15,000 to 140,000 it's a little more than 10% and so this one looks very right to me now one thing I didn't say mention here is we need to round to two decimal places so let's go ahead and round this before anything so let's put round and let's wrap this entire thing now we need to round this to two decimal places so we need to do is do a comma two here because that says rounded two decimal places so let's run this and there we go that all looks correct now we can say this as let's just rename this as percentage just so we don't have that really long name uh it basically makes this the column name that's way too long so we're going to call that percentage now the only thing that we need on our output is company name and percentage so let's come back here and let's put company and let's run this and this looks looks good except we need to order by the company name ascending so we'll say uh order by we'll do company and then we can say ascending although by default order by is in ascending we just I like explicitly writing it so let's run this and then we have Amazon Apple Facebook Google uh Microsoft Tesla so this looks great I think this is our final answer let's go ahead and check our solution and there we go we got the solution correct now if you remember in the last video we checked our profile we earn points towards our badges a medium question you earned 25 points um and so if you're following along you're doing these questions then you should go check uh your profile out because you should have uh in your profile you should have more points now let's go to the next question this one is called separation it says data was input incorrectly into a database the ID was combined with the first name write a query to separate the ID and first name into two separate columns each ID is five characters long all right I've seen this in real databases uh a million times usually when we're like getting data from like an Excel file or something we always have issues with Excel F or CSV files or stuff like that so let's talk about how we are going to actually solve this now we're doing this in MySQL but again you can do this in Python postgrad SQL Microsoft SQL whichever one you are practicing or or you know you have an interview coming up whichever one you have an interview coming up go ahead and use that one now I think one of the main things that I'm interested in right here is that each ID is five characters long um because we need to separate this out so it doesn't matter how long this name is what matters it's kind of kind of the key to this is how long this ID is because if it was 3 four 56 we might have to use something like regular expression to separate that out to extract all the numbers but luckily it's all five characters so with this um we should be able to use something like substring and this will be to pull out numbers and then names so that's what we need uh and then we'll have two separate columns so the output will then be the ID and the first name and I believe that's all we need to do is separate them out into two separate columns and then uh we needed to figure out how to actually separate it so I think that's all we need let's pull this up there we go now I'm going to keep everything so just keeping this column but then we'll separate it out into two and we'll see what this looks like now this substring is going to take a few different parameters first we need to pass through the string now when I say string I mean the column that contains the string so it's going to go through each row of that data we need to select the ID and then we need to specify the start position and the end position now that's where this comes into place uh where that five characters long comes into place so because it's five characters long we should start at position one and then we'll do a comma and end at position five so start at position one and take through position five let's just run this and see if it works there we go so this pulled out just the first five characters in this string now we also need to pull out the full first name and we can actually label this um let me bring this down we'll do as um let's name it something I'll say new underscore ID that's what we'll name it and then we'll do the next one so this one will be substring now we also are going to pass through the ID but this time we're not starting at position one now we need to start at position six but we don't know how many characters are in each name it could be S it could be uh Harry it could be uh es scariot I don't even know know if that's a name but it could be really long um Alexander that's my name long name so we don't know how long it could be so we could put something like 20 here and if we run this it's going to extract it but what if someone's name is longer than 20 characters that's not good luckily though that uh third parameter the end position we can just leave blank and it'll go from the sixth position to the very end of that string so let's run this and that looks really good so we're going to say as uh first underscore name and there we go so let's run this again so we have the new ID and we have the first name we can get rid of this initial one that has everything and I believe this should be our full output now we have this new ID and we have this first name let's go ahead and check our answer and there we go Your solution is correct now these are just two of the medium questions on the platform there are a ton of others so I'm going to leave links in the description to these questions as well as just to the questions page so you can go on there and you can practice and you can really get comfortable writing these out and using them because when you feel comfortable going into that SQL technical interview you're going to do a lot better and you'll know how to talk through these things and if you're ever having trouble with them you can always go to this video explanation where I will walk through it and tell you my exact thought process on how I solve these questions and honestly I think that's one of the best features about the whole platform because when I was first starting out I didn't have this and so I'm really happy that this is here for you so you can really learn it's really just a learning platform to get better at these skills so if you have a technical interview coming up either in python or SQL try out analyst builder.com it is phenomenal I created all the content myself we also have full course on there so you can go ahead and check that out as well in the next video we're going to be going on to the hard questions there going to be quite a big leap from medium to hard and then after that we're also going to be going into very hard questions I would say the very hard questions are more of like a challenge they're very difficult they're a lot of fun but go ahead and check all that out on analyst Builder with that being said if you like this video be sure to like And subscribe and I will see you in the next [Music] video