Solving HARD SQL Interview Questions on Analyst Builder

Key Takeaways

what's going on everybody welcome back to another video today we are going to be solving hard SQL technical interview [Music] questions these hard interview questions are something that you would get in kind of a medium or a senior level data analyst position the easy and the medium are more towards the entry level or slash mid level somewhere in that range the hard ones are not something that you're going to get in the kind of more entry level range these are questions that you might see in kind of a more advanced SQL technical interview I've been on the interview sign where I've interviewed for a ton of data analy positions I was also a hiring manager and then even before that I was on a hiring team where we conducted a ton of sequel technical interviews and so the questions that we're going to look at today are very very similar to ones that I have seen in the real world or even given myself to interviewees with that being said let's up on my screen and take a look all right so we're here on analyst builder.com we're going to go over here to the questions tab we're going to filter to the free ones and then we'll go to the hard ones now now there are a lot more hard ones here on analyst Builder but under the free tab uh we only have three looks like I didn't uh get this one right but we have that one today so we'll see if I get this one right today you can go try out these questions completely for free and we're going to be taking a look at temperature fluctuations and Kelly's third purchase then there's another one called cake vers pie which I think may be the hardest of these three so you might want to go ahead and try to take that one and see if you can get it now this is Kelly's third purchase we'll start with that one and then we'll do temperature fluctuations uh we'll see how quickly I can do these two because these are hard but I think we can do it so let's look at Kelly's third purchase it says at Kelly's ice cream shop Kelly gives a 33% discount on each customer's third purchase right a query to select the third transaction for each customer that receiv received that discount output the customer ID transaction ID amount and the amount after the discount as discounted amount order the output on customer ID in a sending order note transaction IDs occur sequentially the lowest transaction ID is the earliest ID now that's really important we'll have to remember that now before we jump in anything let's um let's go look at the data but then let's start making some notes so we have a customer ID we have the transaction ID and the amount that they spent now this is the amount that eventually will'll need to use to calculate the end uh amount that they paid with the discount so they get 33% off whatever this number is on their third purchase if that if you're tracking that so what we need to do and let's start making some notes one we're going to need to apply a discount so that's going to be 33% we have to identify though the person's third purchase so when they come in three times on that third purchase they then get to get that discount so how can we do that well I I'm almost certain just looking at this data because we have 101 for a customer ID 101 1,1 what we can do is we need to order this transaction ID and then give it some type of rank now because each transaction ID should be unique and we'll double check that but it should be unique we should just be able to use row number but we could also use rank or dense rank um but they should give the exact out same output for each of them it shouldn't matter so I think using just row number um and then filtering when it equals three so we're going to apply a row number based off the customer ID and the transaction ID and then for each customer will give it a row number and then when it's three which is the third transaction that's the one we give the discount to um in our output let's look at what our output is going to be our output is going going to be let's take a look select the third transaction output customer ID transaction ID amount so all columns all columns with uh and I'll just copy this discounted amount so really everything with just that new column and then we need to order by the customer uncore ID so we got a lot to do uh this this definitely doesn't look like I've of course is a difficult question is a hard one but this doesn't look like a super straightforward one so the first thing that we need to do is we have to identify this row number because we cannot apply the discount until we know which data to apply it to the output in the order by will come at the very end so let's look at this let's do um let's do a comma here we'll come down here let's do row number now this is a window function it's it you know if you you haven't used these before you haven't taken like my full course and and you know worked through these things row number uh is a window function that's going to apply to a window or kind of like a group buy is is what I compare it to when you group buy all of those customer IDs with 1,00 and one are going to be grouped into one row with uh a window function they aren't going to be grouped into one row they'll just be in a window where you'll see each row individually and you can apply something to each row instead of grouping it and aggregating the data so it's really unique um and really useful so we're going to do row number and what we need to do this is over the transaction ID and we need to order by order by transaction ID and that's going to be ascending so the earliest one it says lowest transaction ID is the earliest so we need to start with the earliest then go to the highest and pick the third one so let's just run this and I need to do this over I said row number I didn't write that right at all so we'll do over and then we write it so we're doing we're applying this row number the over is the keyword that we use to specify that this is what we are doing it on and so now we have this and we can't just do this because it's applying the row number appropriately based off of only the transaction IDs from from lowest to highest here's the thing though we have to do it per each customer so it's each customer's third so we have to use partition buy before the order buy now partition buy is going to separate it out by the customer ID it's kind of that's kind of like the grouping part um and so we'll use Partition by customer ID and why did I copy that by customer ID and now let's run this and now when we come down it should say 101 1001 and notice that we have this um r row number applying at the customer ID level and then when it gets to the last customer ID and it goes to the next one it restarts so now this is that third person's transaction 101 and then at 102 this is the third transaction now here's the tricky part about trying to then use this row number is I cannot come down here and say where and let's label this we'll say as rowcor num let's run that uh whoops because I have this as blank let's comment that out real quick so I have this row num but I cannot say where row num is equal to three let's try it so it's going to say unknown column it doesn't understand that that's a column and you may be thinking well you know in aggregations with Group by you can use the having statement well let's try the having and let's run this it's says the window function is allowed only in the select list and order by Clause we cannot use in the having so what we need to do is we need to actually make this as uh its own little output is what I'll say now we can do that in two different ways we can use a CTE and we can use comment table expression to kind of store this data down here how it is and then we can query off it later or we can put it in a subquery or if you know we wanted to get really Advanced and we're using um an actual mySQL database we could use something like a temporary table or a view or something we could do other things but for here let's wrap all of this in a um let me come right here let's wrap all of this in a subquery and when you have uh a subquery in a from statement you have to label it so you have to give it a name so we're just going to call as rone numbers and let's select everything and what this is doing is we're selecting everything from this data right down here this table that we've essentially created so what we're going to do is we're going to select everything now what we need to do is we need to say where rowcor num is equal to three and let's run this so now we have each person's row num three this is the third person transaction now this is really good so what we need to do next is we need to then calculate this amount so it gets a 33% discount now so let's select the columns that we actually want in our output we need customer ID we need transaction ID we need the amount now we need to calculate the discounted amount remember we have to label this last one um we'll say as discounted am amount so this next column is going to be this calculation now we have to give a 33% discount so we can't say amount times let me bring this down like this we cannot say amount times 0.33 let's run this and let me see I just spelled transaction ID wrong trans action ID that's it always gets me um this is actually a this is 33% of this number that's what this is now 33% of this number is not a 33% discount we're giving them a 67% discount what we want is 67% of the amount let's run this this right here is 33% off the total amount it's a discount so instead of paying $94 this person only had to pay 62.9 eight now the last thing we need to do the very last thing is order by customer ID and it looks like it already is but I'm going to do it anyways we'll do order by customer ID ascending and let's run this this should be our final output and you know it took a little bit of work to get there we had to use this subquery but I'm pretty sure this is right let's go ahead and check this answer and there we go our solution is correct correct now remember there's other ways to write this there isn't just one way um this is kind of the difficult part about hard interviews or like senior level data analyst interviews for for SQL um technical interviews the difficult thing is there's not only one way to answer it and so it starts getting down to okay what's the best way to solve it walk through your thought process so everything that I just did where I walked through and I said okay I could use any of these but row number makes the most sense for this data understanding the difference between those and why I'm choosing one over the other um is really helpful for the interviewer to understand engage kind of your skill level that's why I recommend you write it out well but also talk about it the thought process is kind of the most important part now if you tried this question you could not get it or you couldn't solve it you can always get a hint um you can take a look at the expected output or you can go up to the video explanation where I walk through this entire question or just go look at the solution and let's see if I wrote it the same way well I called it RN for row number but this is essentially the same although I wrote out the column names it's essentially the same but you could have done a CTE uh with this as well but that is how you would solve this Kelly's third purchase I will leave Link in the description if you want to try that one out now let's go up here let's go to temperature fluctuations so this question says write a query to find all dates with higher temperatures compared to the previous dates yesterday order dates in ascending order okay let's look at the data so we have our date over here and the temperature so it looks like for example this one this is the 2nd of January this temperature was 70 the previous days was 65 so we want to identify this date and I think it's just the date um to find all the dates with higher temperatures it looks like our output is just going to be the dates and I'll write that real quick um output just date column now how are we going to do this how are we going to compare this uh initially there are two things that I think we could do one we could use a window function we could use a lag uh a lag function on this which would look at the previous rows data so if we ordered on the date which it already looks like it's ordered we can use the lag function to look at the previous value so here is 70 then we use the lag function it would pull over 65 over here that would perfect fine uh way to do it the other way we could do it is we could do a self jooin so we could tie the table to itself but instead of doing it where the date is equal to the date we say the date minus one that's another way that we could solve this so you can go ahead and try whichever way you would like to I think I prefer the self join um I just think the last one we did uh a row number which is a window function so I don't want to do another window function right although you could solve this with a window function um I'm going to try a self join so let's pull this over um I think I'm going to do a self join but on the previous day where there's a one day difference so where the day is one day off is what I'll say um then we can use that to compare so use the temperatures to say where one is higher than the other and that should make more sense uh in just a second when we start writing it out but now we also need to order by dates ascending that's it so we have our data down here and in order to do a self jooin we're just going to say um I'm going to say inner join but we can do if there's a different type of join you want to do you can also do that but I'm going to say on temperatures and we need to label these differently so we'll do T1 for temperatures one and then T2 so now it's like we have this table over here which is temperatures we have this table over here that's temperatures and we're going to join them together now what are we going to oh let's do T2 now what are we joining these together on um we're going to be joining this on the dates now there's a few different ways that we can write this but there is a function called Date diff where we can take one date and compare it to a different date and make sure it's one day different so let's go ahead and take a look at that let's do um a join on and we'll do date diff and then we'll do t1. and it's autop populating it for us but date diff comma t2. and then we'll say date right there and it should be a one- day difference let's try this let's run this and the reason why it's not pulling up is because we have all the same uh column names now when that has the same column name it's just showing up as just one one is overlaying the other so what we're going to do is t1. dat comma t1. temperature and let's get rid of that then we're going to label these other ones different so we'll do t2. as date 2 and then we'll copy this and we'll do t2. temperature as temperature to now let's run this and let's see what happens so we have uh this date compared to the previous date this date compared to the previous date so three versus 2 and let's keep going 4 to 3 5 to 4 6 to 5 and so every single date has the previous date now what we can do is we can compare this temperature to this temperature now we can do that in a wear statement or we could just do it in the join we can make it a conditional uh part of the condition in the join maybe I'll write out both but let's say and we'll do t1. uh t1. temperature is greater than t2. temperature so let's run this so now we have 70 compared to 65 and it looks like the other one wasn't as high so the third day is gone 58 compared to 55 5 90 compared to 58 82 compared to 70 88 compared to 82 so these are all of our dates right here in this column that this temperature was higher than the previous day's temperature and so what we should be able to do is just get rid of all these columns and just take the date and let's run this and there we go and all we have to do now is order by and I think it's already correct but I'm just going to I always like to write it out if it's asking us to do it take do this in ascending and so let's run this yeah in ascending order I didn't write that oh yeah here I did I wrote it right here so now this looks correct to me let's go ahead and check our answer and there we go our solution is correct now again there's multiple different ways to solve this genuinely off the top of my head I could think of two probably another one another third option um just off the top of my head cuz I've been using my sequel for a while walking through that in your interview saying I think I could do it in this way or this way but here's why I'm choosing this way that really tells an interviewer this guy knows what he's talking about or a girl this person knows what they're talking about they understand it they get it I can trust that this person will know how to do the work that we're going to give them if we hire them you want to give them a lot of confidence that's all I'm going to say now if you don't know how to do this or you've never done something like this before that's what this platform is for um so that when you get into those interviews or you know you want to learn and go take a course when you get into those interviews you can confidently say I know this skill uh and so if you had trouble with that one you can always go to hints you can always go to the expected output video solution solution um let's see how I solved it here oh I solved it the exact same but I could I could have solved it a different way I think the lag function would have done just as well uh it may have been simpler actually so this is the one that I used but honestly the lag function in a window function may even be better so we solved this Kelly's third purchase we solved this temperature fluctuations I will leave links in the description for both of those go ahead and try those out yourself and again even back in the question section there's this cake versus pie uh which is probably the most difficult of the hard ones uh under the free tier now if you go back under the you know there's a where you can pay for a subscription under those there's like 20 hard questions and they're all very unique very different focusing on data cleaning window functions uh different types of joins and they're all really unique uh and fun to do but this cake versus pie one is really interesting I want um I want you guys to go try this one I'll leave this one in the description as well this really interesting difficult question so those were our two hard SQL interview questions uh they were pretty challenging you know window function and then self-join two things that are a little bit more complex than you'll see in easy and medium questions uh in the next lesson we'll be solving a very hard question so if you have not check out analyst builder.com it is one of the best platform for data analyst I created all the content on there all the courses all the questions and we have so much more coming to the platform if you like this video be sure to like And subscribe below and I will see you in the next [Music] video