- 32 minutes ago
Solving medium SQL interview questions gets a lot easier when you can break each problem into simple steps, and that is exactly what happens here with Analyst Builder. The walkthrough tackles two common SQL technical interview challenges: calculating the percentage of employees laid off from each company, and separating a combined ID and first name into two columns.
The first problem uses a clear percentage calculation, rounding to two decimal places and ordering the results alphabetically by company name. The second shows how substring can split a fixed-length ID from a longer text field, a practical SQL data cleaning skill that comes up often in real databases and interview prep. Along the way, the explanation also reinforces how to talk through your logic during a technical interview, not just type the query.
Created for viewers searching for SQL interview questions, medium SQL problems, MySQL practice, data analyst interview prep, and SQL tutorial walkthroughs. It is a useful study session for anyone practicing technical interviews, learning substring in SQL, or reviewing common data cleaning and percentage calculation queries for Analyst Builder and similar platforms.
The first problem uses a clear percentage calculation, rounding to two decimal places and ordering the results alphabetically by company name. The second shows how substring can split a fixed-length ID from a longer text field, a practical SQL data cleaning skill that comes up often in real databases and interview prep. Along the way, the explanation also reinforces how to talk through your logic during a technical interview, not just type the query.
Created for viewers searching for SQL interview questions, medium SQL problems, MySQL practice, data analyst interview prep, and SQL tutorial walkthroughs. It is a useful study session for anyone practicing technical interviews, learning substring in SQL, or reviewing common data cleaning and percentage calculation queries for Analyst Builder and similar platforms.
Category
📚
LearningTranscript
00:00What's going on, everybody?
00:01Welcome back to another video.
00:02Today, we're gonna be solving
00:03medium SQL technical interview questions.
00:11Now, if you watched the first video in this series,
00:13you saw that we answered
00:14some easy SQL technical interview questions.
00:16Now we're gonna be solving medium level questions.
00:19The medium questions are gonna be a little bit more difficult,
00:21but I will say that if you are practicing
00:23for a SQL technical interview,
00:25I highly recommend practicing the easy
00:26and the medium questions.
00:28But let's not waste any time.
00:30Let's head over my screen
00:30and take a look at our medium level questions.
00:33But actually, before we take a look
00:34at our two questions up here,
00:35you can come over here, go to the free questions,
00:38go to the difficulty and go to moderate.
00:41And these are all the medium level questions
00:43that you can try for free on analystbuilder.com.
00:46If you have not tried out analyst builder,
00:47I highly, highly, highly recommend it.
00:49That is my data analytics learning platform
00:51that I'm extremely proud of.
00:52You can take my full courses
00:53and try out these technical interview questions
00:56all in one place.
00:57But let's head over to our first question,
01:00which is called tech layoffs.
01:02It says, tech companies have been laying off employees
01:04after a large surge of hires in the past few years.
01:07Write a query to determine the percentage of employees
01:09that were laid off from each company.
01:11Output should include the company and the percentage
01:14to two decimal places of laid off employees
01:17ordered by company name alphabetically.
01:20And I think this is extremely accurate
01:22because that just happened.
01:24I'm recording this in late, late, late 2023,
01:29but I'm sure I'll release this in 2024.
01:31And I think you guys know what I'm talking about.
01:33It was just a bad year for 2022, 2023 with all the layoffs.
01:37Now we have Apple, Microsoft, Google, Amazon, Facebook, Tesla,
01:42and these are the employees fired.
01:46And what we're trying to do is output a percentage.
01:49So we need to look at the company
01:52and then percentage of laid off employees.
01:54So if they had zero laid off employees,
01:57the percentage should be zero.
01:59But let's say they had 6,000 of 181,000.
02:02We need to see what percentage
02:04of the entire company size was laid off.
02:07Was it 1%, 2%?
02:09That's what we're trying to determine.
02:12Now we always can use hints and expected output.
02:15If we need help or if we need the video walkthrough,
02:18we can use it, but I don't think we'll need it.
02:20Let's come over here and make some notes
02:22before we get started.
02:23Now, before we write anything,
02:24remember when you're in a technical interview,
02:26whether it's for data analysis,
02:28data engineering, data science, it doesn't matter.
02:30When you are applying for these jobs,
02:32writing it out correctly is important.
02:34But I would say even more importantly,
02:36it's about how you actually talk
02:38through the problem.
02:39Because that really shows your skill level.
02:41If you're walking through it
02:42and you're just typing random stuff
02:44and it kind of makes sense,
02:45but you're not talking out loud,
02:46they may not understand that you really know
02:49what you're talking about.
02:50And so you want to practice these questions,
02:53know what you're talking about,
02:54and while you're solving them,
02:55do what I'm about to do,
02:57which is I'm gonna kind of talk through the steps
02:59that I need to do,
03:00then I'm gonna write it out.
03:01That's what I recommend during actual interviews.
03:04So the first thing that we need
03:06is we need to find the percentage.
03:08Now this is gonna be a calculation.
03:10So I'll write percentage calculation.
03:12Now, how do we determine what the percentage is?
03:16What we need to do is employees fired
03:18divided by the company size times 100.
03:22So it's employees fired divided by comp size times 100.
03:30That's the calculation that we need.
03:33So we are gonna go and do that in just a little bit.
03:37But after that, our output needs something.
03:39So our output needs, and I need to comment this out,
03:43our output needs the company name
03:46and it needs the percentage.
03:49So we're definitely gonna need to include both of those.
03:52And then lastly, we need to order by the company name,
03:56A, S, C, which means ascending, so A to Z.
04:00So this is what we need to do.
04:02Now, let's do one thing first.
04:06Let's just pull this up.
04:08But we don't really need to start looking at the output
04:11or the order by just yet.
04:13Let's keep everything.
04:14We'll keep this comma here.
04:17Let's keep everything,
04:18but let's start working on our calculation.
04:22So let's see if our calculation is correct.
04:24It should be employees fired divided by the company size.
04:29I'm gonna put this all in parentheses
04:32just to make sure we're doing PEMDOS correctly.
04:35And let's multiply it times 100.
04:38So let's run this.
04:40And here we go.
04:42Now, this looks correct, just glancing at this,
04:45because here we have 0% because Apple didn't lay anyone off.
04:503%, 6,000 of 1,800, that also looks correct.
04:53This one, I think, is the most straightforward one,
04:5715,000 into 140,000.
05:00That should be around 10%,
05:02but because it's 15,000 to 140,000,
05:05it's a little more than 10%.
05:07And so this one looks very right to me.
05:11Now, one thing I didn't mention here
05:13is we need to round to two decimal places.
05:16So let's go ahead and round this before anything.
05:18So let's put round, and let's wrap this entire thing.
05:23Now, we need to round this to two decimal places.
05:27So what we need to do is do a comma two here,
05:29because that says round to two decimal places.
05:31So let's run this, and there we go.
05:35That all looks correct.
05:36Now, we can say this as, let's just rename this,
05:39as percentage, just so we don't have that really long name.
05:44It basically makes this the column name
05:46that's way too long.
05:47So we're gonna call that percentage.
05:49Now, the only thing that we need on our output
05:51is company name and percentage.
05:53So let's come back here,
05:55and let's put company, and let's run this.
05:58And this looks good, except we need to order
06:01by the company name ascending.
06:03So we'll say order by, we'll do company,
06:07and then we can say ascending, although by default,
06:11order by is in ascending.
06:13We just, I like explicitly writing it.
06:16So let's run this.
06:17And there we have Amazon, Apple, Facebook, Google,
06:22Microsoft, Tesla.
06:23So this looks great.
06:24I think this is our final answer.
06:26Let's go ahead and check our solution.
06:29And there we go.
06:30We got the solution correct.
06:32Now, if you remember in the last video,
06:34we checked our profile,
06:35we earned points towards our badges.
06:37A medium question earned 25 points.
06:39And so if you're following along,
06:41you're doing these questions,
06:42then you should go check your profile out
06:44because you should have,
06:45in your profile, you should have more points.
06:47Now let's go to the next question.
06:50This one is called separation.
06:52It says, data was input incorrectly
06:55into a database.
06:56The ID was combined with the first name,
06:59write a query to separate the ID and first name
07:02into two separate columns.
07:03Each ID is five characters long.
07:07All right.
07:08I've seen this in real databases a million times.
07:12Usually when we're like getting data
07:14from like an Excel file or something,
07:16we always have issues with Excel files or CSV files
07:19or stuff like that.
07:20So let's talk about how we are going to actually solve this.
07:24Now we're doing this in MySQL,
07:25but again, you can do this in Python, PostgreSQL,
07:28Microsoft SQL server, whichever one you are practicing,
07:32or you have an interview coming up,
07:34whichever one you have an interview coming up,
07:36go ahead and use that one.
07:37Now, I think one of the main things
07:39that I'm interested in right here
07:40is that each ID is five characters long
07:43because we need to separate this out.
07:45So it doesn't matter how long this name is.
07:47What matters that it's kind of,
07:48kind of the key to this is how long this ID is.
07:51Because if it was three, four, five, six,
07:54we might have to use something like regular expression
07:55to separate that out, to extract all the numbers.
07:59But luckily it's all five characters.
08:01So with this, we should be able to use something like substring.
08:06This will be to pull out numbers and then names.
08:11So that's what we need.
08:13And then we'll have two separate columns.
08:15So the output will then be the ID and the first name.
08:20And I believe that's all we need to do
08:23is separate them out into two separate columns.
08:25And then we needed to figure out how to actually separate it.
08:29So I think that's all we need.
08:31Let's pull this up.
08:34There we go.
08:35Now I'm going to keep everything.
08:37So just keeping this column,
08:39but then we'll separate it out into two.
08:40And we'll see what this looks like.
08:42Now, this substring is going to take a few different parameters.
08:47First, we need to pass through the string.
08:49Now, when I say string, I mean the column that contains the strings.
08:53It's going to go through each row of that data.
08:55We need to select the ID.
08:57And then we need to specify the start position and the end position.
09:02Now, that's where this comes into place,
09:04where that five characters long comes into place.
09:07So because it's five characters long,
09:08we should start at position one and then we'll do a comma and end at position five.
09:14So start at position one and take through position five.
09:16Let's just run this and see if it works.
09:20There we go.
09:21So this pulled out just the first five characters in this string.
09:26Now, we also need to pull out the full first name.
09:29And we can actually label this.
09:30Let me bring this down.
09:32We'll do as, let's name it something.
09:35I'll say new underscore ID.
09:38That's what we'll name it.
09:39And then we'll do the next one.
09:41So this will be substring.
09:43Now, we also are going to pass through the ID,
09:45but this time we're not starting at position one.
09:48Now, we need to start at position six.
09:50But we don't know how many characters are in each name.
09:54It could be Sal.
09:55It could be Harry.
09:57It could be Iscariot.
09:58I don't even know if that's a name, but it could be really long.
10:01Alexander.
10:02That's my name.
10:02Long name.
10:03So we don't know how long it could be.
10:05So we could put something like 20 here.
10:08And if we run this, it's going to extract it.
10:10But what if someone's name is longer than 20 characters?
10:14That's not good.
10:15Luckily, though, that third parameter, the end position,
10:19we can just leave blank and it'll go from the sixth position
10:22to the very end of that string.
10:24So let's run this.
10:25And that looks really good.
10:27So we're going to say as first underscore name.
10:31And there we go.
10:32So let's run this again.
10:34So we have the new ID and we have the first name.
10:37We can get rid of this initial one that has everything.
10:42And I believe this should be our full output.
10:44Now we have this new ID and we have this first name.
10:48Let's go ahead and check our answer.
10:50And there we go.
10:51Your solution is correct.
10:53Now, these are just two of the medium questions on the platform.
10:55There are a ton of others.
10:57So I'm going to leave links in the description to these questions,
10:59as well as just to the questions page.
11:01So you can go on there and you can practice and you can really get comfortable
11:05writing these out and using them.
11:06Because when you feel comfortable going into that SQL technical interview,
11:11you're going to do a lot better and you'll know how to talk through these things.
11:14And if you're ever having trouble with them, you can always go to this video explanation
11:18where I will walk through it and tell you my exact thought process on how I solve these questions.
11:24And honestly, I think that's one of the best features about the whole platform,
11:27because when I was first starting out, I didn't have this.
11:30And so I'm really happy that this is here for you.
11:32So you can really learn. It's really just a learning platform to get better at these skills.
11:37So if you have a technical interview coming up, either in Python or SQL,
11:40try out analystbuilder.com. It is phenomenal.
11:42I created all the content myself.
11:44We also have full course on there.
11:46So you can go ahead and check that out as well.
11:48In the next video, we're going to be going on to the hard questions.
11:52And that's going to be quite a big leap from medium to hard.
11:55And then after that, we're also going to be going into very hard questions.
11:58I would say the very hard questions are more of like a challenge.
12:01They're very difficult.
12:02They're a lot of fun, but go ahead and check all that out on Analyst Builder.
12:06With that being said, if you like this video, be sure to like and subscribe,
12:09and I will see you in the next video.
Comments