 What's going on everybody? Welcome back to another video. Today we're going to be solving medium SQL technical interview questions. Now if you watched the first video in this series, you saw that we answered some easy SQL 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 SQL technical interview, I highly recommend practicing the easy and the medium questions. But let's not waste any time. 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 analystbuilder.com. If you have 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 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. Outputs should include the company and the percentage to two decimal places of laid off employees, ordered by company name alphabetically. And I think this is extremely accurate because that just happened. I'm recording this in late, late, late 2023. But I'm sure I'll release this in 2024. And I think you guys know what I'm talking about. It was 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%? 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 all right, 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 times 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 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, just to make sure we're doing PEMDAS 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% because Apple didn't lay anyone off. 3% 6,800. That also looks correct. This one, I think, is the most straightforward one. 15,000 into 140,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 round to 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. 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 good, except we need to order by the company name ascending. So we'll say 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, 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 the last video, we checked our profile, we earn points towards our badges, a medium question, you earn 25 points. And so, if you're following along, you're doing these questions, then you should go check your profile out because you should have, 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 a million times. Usually when we're like getting data from like an Excel file or something, we always have issues with Excel file 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, PostgreSQL, Microsoft SQL Server, whichever one you are practicing 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, because we need to separate this out. So it doesn't matter how long this name is, what matters that it's kind of kind of the key to this is how long this ID is, because if it was three, four, five, six, 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, we should be able to use something like substring. This will be to pull out numbers and then names. So that's what we need. 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 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, because 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, 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 a 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 first name. And we can actually label this. Let me bring this down. We'll do as, 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 will be substring. Now, we also were going to pass through the ID, but this time we're not starting at position one. Now, when you start at position six, but we don't know how many characters are in each name. It could be Sal. It could be Harry. It could be a scary. It's I don't even know if that's a name, but it could be really long. 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's a 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 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 out. But 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 sequel 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 analystbuilder.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 that's 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 analystbuilder. With that being said, if you liked this video, be sure to like and subscribe, and I will see you in the next video.