- 16 minutes ago
Hard SQL interview questions can get tricky fast, and this walkthrough breaks down two real examples from Analyst Builder with clear, interview-style reasoning. The first problem, Kelly’s Third Purchase, uses a window function and row number logic to find each customer’s third transaction and calculate the discounted amount after a 33% discount. The second, Temperature Fluctuations, compares each day’s temperature to the previous day and shows how to solve it with a self join and date difference logic.
Along the way, the solution explains why certain approaches work better than others in a technical SQL interview, especially for medium and senior data analyst roles. It also highlights the thought process behind choosing row number, partition by, ordering, and filtering, plus how to compare sequential dates in MySQL. If you are practicing SQL interview questions, learning window functions, or reviewing self joins and lag-style thinking, this is a useful hands-on guide.
Created for viewers searching for hard SQL interview questions, SQL technical interview practice, MySQL window functions, self join examples, and data analyst interview prep. It is also helpful for anyone studying SQL query writing, advanced database logic, and real-world problem solving for analyst roles.
Along the way, the solution explains why certain approaches work better than others in a technical SQL interview, especially for medium and senior data analyst roles. It also highlights the thought process behind choosing row number, partition by, ordering, and filtering, plus how to compare sequential dates in MySQL. If you are practicing SQL interview questions, learning window functions, or reviewing self joins and lag-style thinking, this is a useful hands-on guide.
Created for viewers searching for hard SQL interview questions, SQL technical interview practice, MySQL window functions, self join examples, and data analyst interview prep. It is also helpful for anyone studying SQL query writing, advanced database logic, and real-world problem solving for analyst roles.
Category
📚
LearningTranscript
00:00What's going on, everybody?
00:01Welcome back to another video.
00:02Today, we are gonna be solving
00:03hard SQL technical interview questions.
00:11These hard interview questions are something
00:13that you would get in kind of a medium
00:14or a senior level data analyst position.
00:17The easy and the medium are more towards the entry level
00:19or slash mid level, somewhere in that range.
00:22The hard ones are not something that you're going to get
00:24in the kind of more entry level range.
00:26These are questions that you might see
00:28in kind of a more advanced SQL technical interview.
00:31I've been on the interview side where I've interviewed
00:33for a ton of data analyst positions.
00:34I was also a hiring manager.
00:36And then even before that, I was on a hiring team
00:38where we conducted a ton of SQL technical interviews.
00:41And so the questions that we're gonna look at today
00:42are very, very similar to ones that I have seen
00:44in the real world or even given myself to interviewees.
00:47With that being said, let's jump on my screen
00:49and take a look.
00:50All right, so we're here on analystbuilder.com.
00:52We're gonna go over here to the questions tab.
00:54We're gonna filter to the free ones.
00:56And then we'll go to the hard ones.
00:59Now, there are a lot more hard ones here on analyst builder,
01:03but under the free tab, we only have three.
01:07Looks like I didn't get this one right,
01:09but we have that one today.
01:10Well, so we'll see if I get this one right today.
01:12You can go try out these questions completely for free.
01:15And we're gonna be taking a look at temperature fluctuations
01:17and Kelly's third purchase.
01:19And then there's another one called cake versus pie,
01:20which I think may be the hardest of these three.
01:23So you might wanna go ahead and try to take that one
01:26and see if you can get it.
01:27Now, this is Kelly's third purchase.
01:29We'll start with that one,
01:29and then we'll do temperature fluctuations.
01:32We'll see how quickly I can do these two,
01:34because these are hard, but I think we can do it.
01:38So let's look at Kelly's third purchase.
01:40It says, at Kelly's ice cream shop,
01:42Kelly gives a 33% discount
01:43on each customer's third purchase.
01:45Write a query to select the third transaction
01:47for each customer that received that discount.
01:50Output the customer ID, transaction ID, amount,
01:53and the amount after the discount as discounted amount.
01:57Order the output on customer ID in ascending order.
02:00Note transaction IDs occur sequentially.
02:03The lowest transaction ID is the earliest ID.
02:07Now that's really important.
02:08We'll have to remember that.
02:10Now, before we jump in anything,
02:11let's go look at the data,
02:13but then let's start making some notes.
02:15So we have a customer ID.
02:17We have the transaction ID and the amount that they spent.
02:22Now, this is the amount that eventually we'll need to use
02:25to calculate the end amount that they paid with the discount.
02:31So they get 33% off whatever this number is
02:34on their third purchase, if you're tracking that.
02:38So what we need to do,
02:39and let's start making some notes.
02:41One, we're gonna need to apply a discount.
02:44So that's gonna be 33%.
02:47We have to identify though, the person's third purchase.
02:51So when they come in three times on that third purchase,
02:54they then get to get that discount.
02:57So how can we do that?
02:59Well, I'm almost certain, just looking at this data,
03:02because we have 1001 for a customer ID, 1001, 1001.
03:08What we can do is we need to order this transaction ID
03:11and then give it some type of rank.
03:14Now, because each transaction ID should be unique,
03:17and we'll double check that, but it should be unique.
03:19We should just be able to use row number,
03:21but we could also use rank or dense rank,
03:25but they should give the exact same output for each of them.
03:29It shouldn't matter.
03:30So I think using just row number
03:33and then filtering when it equals three.
03:38So we're gonna apply a row number
03:40based off the customer ID and the transaction ID.
03:44And then for each customer, we'll give it a row number.
03:47And then when it's three, which is the third transaction,
03:50that's the one we give the discount to.
03:54In our output, let's look at what our output is gonna be.
03:57Our output is going to be, let's take a look.
04:02Select the third transaction, output, customer ID,
04:05transaction ID, amount.
04:06So all columns, all columns with,
04:12and I'll just copy this, discounted amount.
04:16So really everything with just that new column.
04:18And then we need to order by the customer underscore ID.
04:24So we got a lot to do.
04:27This definitely doesn't look like,
04:29of course, it's a difficult question, it's a hard one,
04:31but it doesn't look like a super straightforward one.
04:35So the first thing that we need to do
04:37is we have to identify this row number
04:40because we cannot apply the discount
04:43until we know which data to apply it to.
04:46The output and the order by will come at the very end.
04:50So let's look at this.
04:51Let's do a comma here.
04:54We'll come down here.
04:55Let's do row number.
04:56Now this is a window function.
04:58It's, you know, if you haven't used these before,
05:01you haven't taken like my full course
05:03and, you know, worked through these things,
05:05row number is a window function
05:07that's gonna apply to a window
05:09or kind of like a group by is what I compare it to.
05:12When you group by all of those customer IDs
05:15with 1001 are gonna be grouped into one row.
05:18With a window function,
05:20they aren't gonna be grouped into one row.
05:22They'll just be in a window
05:24where you'll see each row individually.
05:26And you can apply something to each row
05:28instead of grouping it and aggregating the data.
05:31So it's really unique and really useful.
05:34So we're gonna do row number.
05:36And what we need to do this is over the transaction ID
05:40and we need to order by, order by, transaction ID.
05:44And that's gonna be ascending.
05:46So the earliest one,
05:47it says lowest transaction ID is the earliest.
05:50So we need to start with the earliest,
05:51then go to the highest and pick the third one.
05:53So let's just run this.
05:56And I need to do this over.
05:57I said row number.
05:59I didn't write that right at all.
06:00So we'll do over and then we write it.
06:03So we're doing, we're applying this row number.
06:06The over is the keyword that we use to specify
06:09that this is what we are doing it on.
06:12And so now we have this
06:14and we can't just do this
06:17because it's applying the row number appropriately
06:19based off of only the transaction IDs
06:22from lowest to highest.
06:24Here's the thing though.
06:25We have to do it per each customer.
06:27So it's each customer's third.
06:29So we have to use partition by before the order by.
06:32Now partition by is going to separate it out
06:35by the customer ID.
06:37It's kind of, that's kind of like the grouping part.
06:40And so we'll use partition by customer ID.
06:45And why did I copy that?
06:47By customer ID.
06:48And now let's run this.
06:51And now when we come down,
06:53it should say 1001, 1001.
06:56And notice that we have this row number
06:59applying at the customer ID level.
07:02And then when it gets to the last customer ID
07:05and it goes to the next one, it restarts.
07:08So now this is that third person's transaction, 1001.
07:11And then in 1002, this is the third transaction.
07:15Now here's the tricky part
07:17about trying to then use this row number
07:20is I cannot come down here and say where,
07:24and let's label this.
07:25We'll say as row underscore num.
07:28Let's run that.
07:30Whoops.
07:31Because I have this as blank.
07:33Let's comment that out real quick.
07:36So I have this row num,
07:38but I cannot say where row num is equal to three.
07:44Let's try it.
07:46So it's going to say unknown column.
07:47It doesn't understand that that's a column.
07:50And you may be thinking,
07:51well, you know, in aggregations with group by,
07:53you can use the having statement.
07:55Well, let's try the having.
07:57And let's run this.
07:59It says the window function is allowed
08:00only in the select list and order by clause.
08:03We cannot use in the having.
08:04So what we need to do
08:06is we need to actually make this
08:08as its own little output is what I'll say.
08:12Now we can do that in two different ways.
08:14We can use a CTE
08:15and we can use common table expression
08:17to kind of store this data down here,
08:19how it is.
08:20And then we can query off it later
08:22or we can put it in a subquery.
08:25Or if, you know,
08:26we want it to get really advanced
08:27and we're using an actual MySQL database,
08:31we could use something like a temporary table
08:33or a view or something.
08:35We could do other things.
08:36But for here,
08:38let's wrap all of this in a,
08:43let me come right here.
08:44Let's wrap all this in a subquery.
08:48And when you have a subquery in a from statement,
08:52you have to label it.
08:54So you have to give it a name.
08:55So we're just going to call as row numbers.
08:58And let's select everything.
09:00And what this is doing
09:02is we're selecting everything
09:04from this data right down here,
09:08this table that we've essentially created.
09:11So what we're going to do
09:12is we're going to select everything.
09:14Now, what we need to do
09:16is we need to say
09:17where row underscore num is equal to three.
09:22And let's run this.
09:23So now we have each person's row num three.
09:28This is the third person's transaction.
09:30Now, this is really good.
09:31So what we need to do next
09:33is we need to then calculate this amount.
09:36So it gets a 33% discount now.
09:38So let's select the columns
09:40that we actually want in our output.
09:42We need customer underscore ID.
09:45We need transaction underscore ID.
09:48We need the amount.
09:50Now we need to calculate
09:52the discounted amount.
09:53Remember, we have to label this last one.
09:56We'll say as discounted amount.
09:59So this next column
10:00is going to be this calculation.
10:01Now we have to give you 33% discount.
10:05So we can't say amount times.
10:09Let me bring this down like this.
10:12We cannot say amount times 0.33.
10:16Let's run this.
10:18And let me see.
10:19I just spelled transaction ID wrong.
10:23Trans action ID.
10:25That's, it always gets me.
10:28This is actually a,
10:30this is 33% of this number.
10:33That's what this is.
10:35Now, 33% of this number
10:37is not a 33% discount.
10:39We're giving them a 67% discount.
10:41What we want is 67% of the amount.
10:46Let's run this.
10:48This right here is 33% off the total amount.
10:52It's a discount.
10:54So instead of paying $94,
10:55this person only had to pay 62.98.
10:59Now the last thing we need to do,
11:01the very last thing is order by customer ID.
11:04And it looks like it already is,
11:06but I'm going to do it anyways.
11:09We'll do order by customer ID ascending.
11:14And let's run this.
11:15This should be our final output.
11:18And you know,
11:19it took a little bit of work to get there.
11:21We had to use this sub query,
11:22but I'm pretty sure this is right.
11:23Let's go ahead and check this answer.
11:26And there we go.
11:27Our solution is correct.
11:29Now, remember there's other ways to write this.
11:32There isn't just one way.
11:34This is kind of the difficult part
11:36about hard interviews
11:37or like senior level data analyst interviews
11:39for SQL technical interviews.
11:41The difficult thing is there's not only one way to answer it.
11:44And so it starts getting down to,
11:47okay, what's the best way to solve it?
11:49Walk through your thought process.
11:51So everything that I just did
11:53where I walked through and I said,
11:54okay, I could use any of these,
11:56but row number makes the most sense for this data.
11:59Understanding the difference between those
12:01and why I'm choosing one over the other
12:04is really helpful for the interviewer
12:06to understand and gauge kind of your skill level.
12:08That's why I recommend you write it out well,
12:11but also talk about it.
12:12The thought process is kind of the most important part.
12:16Now, if you tried this question,
12:17you could not get it or you couldn't solve it.
12:19You can always get a hint.
12:21You can take a look at the expected output,
12:23or you can go up to the video explanation
12:25where I walked through this entire question,
12:28or just go look at the solution.
12:29And let's see if I wrote it the same way.
12:31Well, I called it RN for row number,
12:33but this is essentially the same,
12:35although I wrote out the column names.
12:36It's essentially the same,
12:38but you could have done a CTE with this as well.
12:41But that is how you would solve
12:42this Kelly's third purchase.
12:44I will leave a link in the description
12:45if you wanna try that one out.
12:47Now, let's go up here.
12:48Let's go to temperature fluctuations.
12:51So this question says,
12:53write a query to find all dates with higher temperatures
12:55compared to the previous dates yesterday.
12:59Order dates in ascending order.
13:01Okay, let's look at the data.
13:03So we have our date over here and the temperature.
13:06So it looks like, for example, this one.
13:08This is the 2nd of January.
13:11This temperature was 70.
13:12The previous days was 65.
13:14So we want to identify this date.
13:17And I think it's just the date
13:20to find all the dates with higher temperatures.
13:22It looks like our output is just gonna be the dates.
13:25And I'll write that real quick.
13:28Output, just date column.
13:31Now, how are we gonna do this?
13:33How are we gonna compare this?
13:35Initially, there are two things that I think we could do.
13:38One, we could use a window function.
13:40We could use a lag function on this,
13:44which would look at the previous rows data.
13:47So if we ordered on the date,
13:48which it already looks like it's ordered,
13:50we can use the lag function to look at the previous value.
13:53So here is 70.
13:54Then we use the lag function,
13:55it would pull over 65 over here.
13:58That would perfectly fine way to do it.
14:00The other way we could do it is we could do a self-join.
14:03So we could tie the table to itself,
14:06but instead of doing it where the date is equal to the date,
14:09we say the date minus one.
14:11That's another way that we could solve this.
14:13So you can go ahead and try whichever way you would like to.
14:16I think I prefer the self-join.
14:20I just think the last one we did a row number,
14:23which is a window function.
14:24So I don't wanna do another window function, right?
14:27Although you could solve this with a window function.
14:29I'm gonna try a self-join.
14:31So let's pull this over.
14:32I think I'm gonna do a self-join,
14:36but on the previous day where there's a one day difference.
14:40So where the day is one day off,
14:44is what I'll say.
14:45Okay, then we can use that to compare.
14:49So use the temperatures to say where one is higher than the other.
14:59And that should make more sense in just a second
15:02when we start writing it out.
15:03But now we also need to order by dates ascending.
15:07That's it.
15:08So we have our data down here.
15:11And in order to do a self-join,
15:13we're just gonna say, I'm gonna say inner join.
15:16But we can do, if there's a different type of join you wanna do,
15:18you can also do that.
15:19But I'm gonna say on temperatures,
15:21and we need to label these differently.
15:23So we'll do T1 for temperatures one and then T2.
15:27So now it's like we have this table over here,
15:29which is temperatures.
15:30We have this table over here, this temperatures,
15:32and we're gonna join them together.
15:34Now what are we gonna, oh, let's do T2.
15:36Now what are we joining these together on?
15:40We're gonna be joining this on the dates.
15:43Now there's a few different ways that we can write this,
15:46but there is a function called date diff,
15:48where we can take one date and compare it to a different date
15:52and make sure it's one day different.
15:55So let's go ahead and take a look at that.
15:58Let's do a join on, and we'll do date diff,
16:03and then we'll do T1.date,
16:07and it's auto-populating it for us,
16:09but date diff, comma, T2 dot,
16:13and then we'll say date right there.
16:15And it should be a one day difference.
16:17Let's try this.
16:18Let's run this.
16:21And the reason why it's not pulling up
16:23is because we have all the same column names.
16:26Now, when it has the same column name,
16:29it's just showing up as just one.
16:32One is overlaying the other.
16:33So what we're gonna do is T1.date, comma,
16:38T1.temperature, and let's get rid of that.
16:43Then we're gonna label these other ones different.
16:45So we'll do T2.date as date two,
16:50and then we'll copy this,
16:54and we'll do T2.temperature as temperature two.
17:00Now let's run this, and let's see what happens.
17:03So we have this date compared to the previous date,
17:08this date compared to the previous date,
17:11so three versus two,
17:13and let's keep going four to three,
17:15five to four, six to five,
17:17and so every single date has the previous date.
17:21Now what we can do is we can compare this temperature
17:24to this temperature.
17:26Now we can do that in a where statement,
17:28or we could just do it in the join.
17:30We can make it a conditional,
17:31part of the condition in the join.
17:34Maybe I'll write out both,
17:35but let's say and we'll do T1.temperature
17:43is greater than T2.temperature.
17:47So let's run this.
17:49So now we have 70 compared to 65,
17:53and it looks like the other one wasn't as high,
17:55so the third day is gone.
17:5758 compared to 55,
17:5990 compared to 58,
18:0182 compared to 70,
18:0388 compared to 82.
18:04So these are all of our dates
18:06right here in this column
18:07that this temperature
18:09was higher than the previous day's temperature.
18:12And so what we should be able to do
18:15is just get rid of all of these columns
18:18and just take the date.
18:20And let's run this.
18:21And there we go.
18:23And all we have to do now is order by,
18:26and I think it's already correct,
18:29but I'm just gonna,
18:30I always like to write it out
18:32if it's asking us to do it.
18:33Okay, do this in ascending.
18:36And so let's run this.
18:38Yeah, in ascending order.
18:39I didn't write that.
18:40Oh yeah, here I did.
18:41I wrote it right here.
18:42So now this looks correct to me.
18:44Let's go ahead and check our answer.
18:46And there we go.
18:47Our solution is correct.
18:49Now again,
18:49there's multiple different ways to solve this.
18:51Genuinely off the top of my head,
18:53I could think of two,
18:54probably another one,
18:55another third option.
18:56Just off the top of my head
18:57because I've been using MySQL for a while.
18:59Walking through that in your interview saying,
19:02I think I could do it in this way or this way,
19:04but here's why I'm choosing this way.
19:06That really tells an interviewer,
19:08this guy knows what he's talking about.
19:10Or girl,
19:11this person knows what they're talking about.
19:13They understand it.
19:14They get it.
19:14I can trust that this person
19:16will know how to do the work
19:18that we're gonna give them if we hire them.
19:20You wanna give them a lot of confidence.
19:22That's all I'm gonna say.
19:23Now,
19:24if you don't know how to do this
19:25or you've never done something like this before,
19:27that's what this platform is for.
19:28So that when you get into those interviews
19:30or you wanna learn and go take a course,
19:33when you get into those interviews,
19:34you can confidently say,
19:35I know this skill.
19:36And so if you had trouble with that one,
19:38you can always go to hints.
19:40You can always go to the expected output,
19:42video solution,
19:43solution.
19:45Let's see how I solved it here.
19:46Oh, I solved it the exact same.
19:47But I could have solved it a different way.
19:49I think the lag function
19:51would have done just as well.
19:53It may have been simpler, actually.
19:55So this is the one that I used,
19:57but honestly,
19:58the lag function in a window function
20:00may even be better.
20:02So we solved this Kelly's third purchase.
20:04We solved this temperature fluctuations.
20:07I will leave links in the description
20:09for both of those.
20:09Go ahead and try those out yourself.
20:11And again,
20:12even back in the question section,
20:14there's this cake versus pie,
20:16which is probably the most difficult
20:18of the hard ones
20:19under the free tier.
20:20Now,
20:21if you go back under the,
20:23you know,
20:23there's a,
20:24where you can pay for a subscription.
20:25Under those,
20:26there's like 20 hard questions
20:28and they're all very unique,
20:29very different,
20:30focusing on data cleaning,
20:31window functions,
20:32different types of joins.
20:33And they're all really unique
20:34and fun to do.
20:36But this cake versus pie one
20:37is really interesting.
20:39I want,
20:40I want you guys to go try this one.
20:41I'll leave this one
20:42in the description as well.
20:43This really interesting,
20:44difficult question.
20:45So those were our two hard
20:47SQL interview questions.
20:49They were pretty challenging,
20:50you know,
20:50window function
20:51and then self-join.
20:52Two things that are
20:53a little bit more complex
20:54than you'll see
20:54in easy and medium questions.
20:56In the next lesson,
20:58we'll be solving
20:58a very hard question.
21:00So if you have not,
21:00check out analystbuilder.com.
21:02It's one of the best platform
21:03for data analysts.
21:03I created all the content on there,
21:05all the courses,
21:06all the questions,
21:07and we have so much more
21:07coming to the platform.
21:09If you like this video,
21:10be sure to like
21:11and subscribe below
21:12and I will see you
21:12in the next video.
21:15Bye.
Comments