 What's going on everybody? Welcome back to another video. Today, we are doing medium level leak code questions. Now, in the first video of this series, we did easy questions on leak code and they were fairly easy, somewhere a little tricky. The medium ones are probably gonna be quite a bit harder and then in the next episode, I'm gonna move on to the hard questions and see if I can actually tackle those. Let's jump over to my screen and let's see what questions we're gonna be looking at today. As you can see, I only have two questions on here. I am not sure how long these are gonna take and as you know, these are not gonna be super cut like I do in my other videos and so this could take a little bit of time and so I wanted to give myself just two questions to do on this level and when we go over to hard, I think I'll try to do two as well. We'll see how this goes. But I originally had three and the third one was very similar to one of the easy questions. It looked like a very similar type of logic and so I didn't wanna do the same thing over again. So I'm just sticking with two today. Let's look at our very first one. This is rank scores. If you know, I'm not paying for this. This is not sponsored in any way and I'm very cheap. I'm not, as much as I love you guys, I am not paying $35 to make this video for like a month subscription. That's just too much money. So I'm sticking with the free ones and there aren't a ton of amazing diversity in the free ones. They're all just medium level, not super hard, similar style. So I tried to choose two that seemed interesting. Now we'll see how that goes. So let's read through this one really quick. It says write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. So right off the bat, there is something called dense rank. So when anything talks about ranking, my obviously I go right to rank, row number, dense rank, stuff like that. But it's saying if there's a tie between two scores, they should have the same ranking. I'm almost certain we're gonna use something called dense rank for this. And real quick, what that is, is if they have two, if there's like, you know, ages 25, ages, or that's a really bad one. If the salary is 100,000, salary is 100,000, 95, 90, 90. The two that are 100s are gonna both be ranked number one, cause they tie. So that's kind of what dense rank does, whereas rank wouldn't do that. So note that after a tie, the next ranking number should be the next consecutive integer value. So if there's a, yeah, so that again, so instead of going one, one, three, it needs to go one, one, two. Again, that's what dense rank does. In other words, there should be no holes between ranks. Like I said, one, one, three, that there's a hole cause there's no number two. We don't want that is what it's saying. So we have this ID and score here. The ID is one, two, three, five, six. Super easy scores. It looks like GPAs. Could be GPAs, I don't know. For example, given these scores table, your query should generate the following report, order by highest score. So let's take a look at this. So we don't care about the ID at all it looks like, we're just taking the score, ordering it, and then ranking it. Oops, ranking it. Important note from MySQL solutions to escape reserved words used for column names, you can use an apostrophe before and after keyword for example rank. We're not gonna be using MySQL. I'm sticking with Microsoft SQL server as I did in the last video staying consistent. Although I did say I might do MySQL, but I don't think I'm gonna do that. I lied to you guys. So like I said, I'm gonna use dense rank and it really, I think this is gonna be super simple. Because I'm not, there's nothing I need to partition by. I'm trying to think of other ways I could potentially solve this. I may be able, I probably could do some type of, no, can't do row number. I'm just trying to think through if there's anything else that I'm missing that I could be doing. Dense rank was like just like super obvious to me right when I'm start, right when I heard this problem. So I'm trying to think of there's anything, any other solutions. I might just get writing the dense rank query to see if that works. And I might have some ideas along the way of other options. So let's start doing that and just see where that takes us. So I'm gonna do select, what is it, score and let's real quick. I never write this how I should. I'm gonna try to do proper formatting. What is this? This is from scores. Okay. I'm gonna use dense rank. And so let me see how I'm gonna do this. I think I'm just gonna literally write, right I am just gonna write dense rank, oops. And I'm just gonna do that on score. And again, I'm not using partition by or anything. I'm just making sure, cause this seems so simple. I'm not gonna actually include a lot of normal things that I kind of would normally use when I use something like dense rank because this is such a simple table. Okay, let's do dense rank over. And then we'll do, I think the only thing I really need to do it over is I need to order these scores descending so that we have highest to lowest. I think that's it. That genuinely may be it. So I'm just gonna do order by score descending. We can just keep it like that. Actually I need to name it rank, just like that. So score, maybe I need to do it like this as score. Unless this one had a score. No it did, okay. Okay, so let's see really quick if this works. I have a feeling this is just gonna be a super easy one, but let's run this and see what happens. If this is a medium level one, okay, so it worked. If this is a medium level one, I think this is one of the easier medium level ones unless you've just never used things like rank, dense rank, row number, those types of things or have experience with that. I mean, I have to do some research on what that is and how to use it, but I've used that before and so this one was pretty cut and dry. Again, maybe I should have added that third one. I don't know how long this next one is gonna take. Glancing at it looked a little bit more difficult, but again, I haven't worked through these and so we shall see. So let's move on to the next one, number two. See if this one is a little bit more of a medium problem. All right, before we move on to the next question, I wanted to mention that in that ranked score is there was not a solution available and so I didn't have anything to review, but this one does have a solution to review and so we will review that one after I do this question if I'm able to get it right or wrong, we'll still look and see what they decided to do, whether it's the same or different than mine. Let's read through this one really quick. This is department highest salary. So it says the employee table holds all employees. Every employee has an ID, a salary and there is also a column for the department ID, okay. So super simple table. The thing was department highest salary, so yeah. So we have a department table that holds all the departments of the company. So again, just two. It says write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, your SQL query should return the following rows. Order of rows does not matter. And the explanation is Max and Jim both have the highest salary in the IT department and Henry has the highest salary in the sales department. Okay, so we are basically looking for the highest sales in either department. But it threw us a little curve ball right here because it looks like we have two that tie, okay. Which is semi fortuitous as we just did a dense rank in the last one. And so I'm pretty sure we're gonna use that in this one. But this one looks like it's gonna be a little bit more complicated than the last one. My initial thoughts is that I'm gonna have to use some type of sub query. I'm obviously gonna have to use a join to join these two tables on this department ID here and ID here. But I'm gonna have to use probably some type of, because we have multiple departments, I'm gonna have to use partition. And when I do that, I like to use either a CTE or a sub query or a temp table. I've never done a temp table in Leaco, but well, that's not true. I think I have done a, but I haven't done it in a while. So I'm kind of forgetting if I can do that or not. Yeah, I think I'm leaning towards doing a CTE off of this table and basically doing the dense rank partition by on that one. And then joining that CTE with this table down here and just saying where rank equals one or dense rank equals one. I think that should work. Because again, it's very similar to the, it's not super similar, but it's kind of similar to the last one. Obviously there are no joins. I didn't have to do a CTE or a sub query or whatever I'm gonna do. I think though I'm gonna stick with, I think I am gonna stick with a CTE. So let me just start running it out. Again, there are probably other options. They may have done something differently than we did, but we're just gonna do our own thing. Oh, excuse me. I hope you didn't hear that. So let's start writing this. Let's do select, oh, no, we're doing a CTE. So let's do width and we'll do, I'm just gonna do CTE. That just makes it easy for me. As, and let's do an open parenthesis. Let's do it like this. And so let's start building our select statement to go within the CTE. Again, this is where we're gonna put the, it's kind of like a sub query. If you notice sub queries is very similar to a sub query except we're just organizing it a little bit differently. So we're gonna do select and we need, what do we need? Department, employee and salary. Okay, so I need, wait, give me a second. I'm trying to figure this out. So department is the name and the department, but I'm gonna need, for this, I'm gonna need the department ID, okay? And then, okay, so I'm gonna need that and then I'm gonna need obviously this employee, which is name here and then the salary. Okay, well, it's actually a good thing I'm doing. All of a sudden it's CTE because I'm getting confused already. So the first thing I need is, I can just do the department ID, I guess, if I'm trying to keep it in order. So I'm gonna do department ID as, actually they use this column so I can keep it as department ID. And then I need employee name, okay. So it's gonna be name, I believe. Sorry, I'm kind of all over the place on this one and I need it as employee. And again, when we query, when we join the CTE with this department table later, I can just call it employee and so it'll make more sense once I get further along. I'm just writing this out and so, it may not make sense as I'm writing it. So stick with me for a second. Let's see, and then we need the salary. I believe that's salary in both, correct. Okay, now this is where I'm gonna need to do the dense rank, I'm basically, pretend we're taking this table, I'm just adding a rank here. It's gonna rank these salaries. That's what we're gonna be ranking is the salary. But we're doing it based off because we need to partition it by the department right here. So that's why we have to use the partition by. So let's start writing this out and I'm gonna say dense as we did in the last one, dense rank and then it's gonna be over and then this is where we're doing the partition by. And we're gonna do that on, let's see, what's it called? Department ID. So department ID and that's, and again, when we do that on the department ID, it's gonna do, when we join it to here, it's gonna work perfectly because we're just grabbing the name from that and it will tell us what's the number one rank in both the IT table or IT department and sales department. That's kind of the logic here. But we need to do this ordering by and then we'll do salary. And as we did in the last one, it looks they're doing the highest. So we're doing descending. So we wanna rank, if we did ascending, the lowest one would get the rank number one. We don't want that. We want the highest one to get rank number one. So we're doing descending here. And so it doesn't matter what we call it. I'll just do it as dense rank. And that's the whole, no, I have to specify what table it's from. So you're like this from employee. So that's the entire thing. I'm pretty sure. Oops. Okay. So now I just need to join to the table to this department table. So I'm gonna say, I don't need to join. I need to start building the query on it, basically. So I'm gonna select, let's skip that, because I just want to, I'll come back to that later. So we're gonna select it from the CTE. I'll keep that as is. And we'll just join that to the department table. And we'll call that DEP for short just to keep it simple. And that'll be on CTE.DEPARTMENT ID is equal to DEP.ID, okay. And then we want to, so this is now combining these tables. Now we need to specify what columns we want. And so the ones we actually want returned are just the ones that we see down here, department employee salary. So that's the only ones that we're gonna pull. Obviously for the department, we're looking at this name. So we're gonna go DEP.NAME comma employee. We can do CTE.Employee. It probably doesn't make a difference, but I'm gonna do it anyways, just so you can really easily see it. Then CTE.salary. And then all we need to do is do where dense rank is equal to one. And this should be it. Let me see. I'm just reading through it one more time. I guess I can read it with you. So we're building the CTE. I'm selecting the department ID, the name as employee because employee is down here. Salary, building the dense rank partition. I spelled it right, it's not in blue. We'll see if I spelled that right or didn't spell that, it looks correct, but it's not blue for some reason. So I'm not sure what's going on there. So we'll see if that works as dense rank employee. That's the employee table, good. So then I'm selecting, so then again, we're building this out. So I'm selecting the department name, which is this department right here, the employee, which was the name. I changed it to employee, so the employee. And then the salary and then we're just choosing where the dense rank equals one. This should work. Let's see if this does work. Okay, so we got the wrong answer. Let's see what is wrong with it. So we have name, employee. Ah, it's because this name here is as name, it's supposed to be department. That might have been it, because, oh, whoa, wait. So we have IT, GIMP at 90,000, IT, GIMP 90,000, IT max 90,000, IT max 90,000, sales Henry. Okay, so it's in a different order. Maybe I need to order it by department. I don't know. Let me try that real quick, oops. Or maybe I needed to do that on the last one. I don't know, let me try order by department. IT is, so we'll do ascending. Let's see if this works. I think that was the mistake, so we'll see. And there we go, that was the issue. I figure that was something small because the output looks super similar. So that one was a little bit more tricky than the first one. I will say the first one was, by all accounts, pretty simple. I mean, you guys saw it, it was very, very simple. The second one was a little bit more tricky. I needed to use the partition by, I used the CTE. Lots of changing of names, like aliasing and stuff, which always trips me up, I always forget something. So overall, I think I did pretty good. Let me check how much time I've actually spent on this. Okay, so not long, but long enough to where I don't think I'd be able to fit another one in. And the third one, like I said, it wasn't super relevant, and so I didn't include it. It was very similar to one of the easy questions, but I hope this was helpful. I hope this gave you kind of a glimpse into what a medium question is. I think, honestly, I am feeling pretty confident about going into these hard questions. The easy ones were like, I don't know, they were a little bit more challenging, maybe I just wasn't ready. The mediums ones, this didn't throw me off, they didn't seem super, super hard. Maybe I needed the paid version to get the harder ones, I don't know. But there are free hard ones that we will be trying in the next part of the series. And so I'm super excited about that. I have a newfound confidence that I'm gonna do extremely well. And if I mess up, this might be the end of my career. I hope that doesn't happen. This hat is actually an indicator of who you might be seeing on Thursday in the next Alex The Analyst Show. It is gonna be a really good one, don't miss it. Thank you guys so much for watching. I really appreciate it, and I will see you in the next video.