Introduction
Question: leetcode.com/problems/rising-temperature/
In this video I solve and explain a leetcode SQL question using MySQL query. This question has been asked in Apple, Facebook, Amazon, Google, Adobe, Microsoft, Adobe interviews or what we popularly call FAANG interviews.
I explain the related concept as well. This question includes points to keep in mind to develop SQL queries.
LeetCode is the best platform to help you enhance your skills, expand your knowledge and prepare for technical interviews.
If you found this helpful, Like and Subscribe to the channel for more content.
#LeetCodeSQL #FAANG #SQLinterviewQuestions
Content
Hey guys welcome back to the channel, so this channel every data science is all about trying to learn the different concepts of learning designs for practicing.
A lot of questions in this video I am going to solve this question on read code regarding Rising temperature and try to walk you through how we can develop solutions to such problems.
The difficulty level of this question is easy, and this question has been asked in Amazon as as well as Adobe interviews over the past six months: okay, let's Jump Right In.
We are given a table called weather with three different columns, ID record date, temperature ID, being the primary key for this table.
This table contains information about the temperature.
On a certain day, we are asked to write a SQL query to find all dates, ID with higher temperatures compared to its previous dates.
That is yesterday.
The order of the result does not matter right.
So, let's go through this example right.
So here we have all the record dates, one after the other right, so basically, first of January, 2015, second third, fourth Etc so obviously which all uh on what all dates.
The temperature was more than the previous day, so here right, so this is second of January.
It was more than third, it was not like it was lesser and then for fourth, it was more than the third.
So basically, these are the second of January and fourth of January are the dates which has temperature higher than the their previous dates right.
So the IDS are two and four, and that is what you should have in the output.
Okay.
So to solve this question and to generate the query for this right, what we can do is we can create a fourth column and basically use the lead and lag kind of functions.
That window functions that we have in SQL to basically try to find out.
Okay, what was the temperature previously right? But if you think about it here, the record dates are given like every date is one day after the other right.
So like it's consecutive, but it is possible that let's say uh, you know we have uh our after this.
Fourth of January 2015, you have, let's say 6th of January 2015 and let's say the temperature was 40.
So should that be included, because if you just you know, use the lag or lead part, so obviously 40 is greater than 30 right so, but that should that ID, 5 or 6 or whatever ID is associated with that 6th of January 2015.
Should that be in the output? No, because yesterday's days for 6th of January would is going to be fifth right and you don't have data for that, so you cannot return the ID belonging to 6th of January right.
So, if you think about it, even though this question looks easy, but it is not that straightforward, as you might be thinking in the first go right: okay, let's start building this right.
So from this table called weather, let's return all the three columns right and now we need to make sure that we generate certain columns so that our logic of you know checking two things right.
The temperature should be greater than the previous one like the previous day, and you need to make sure that the temperature that you are checking is of the previous day and not you know uh some days before the day.
You are checking right.
So let you know, let me just write this and like it would be more clear.
So let's say we need to find out okay, so we have the ID regarded and temperature.
Let's say we want to find the yesterday's date so like for each of the date right.
What is the one day before this, irrespective of you know, uh like whether it is present in this table or not.
You know, because it is going to help us understand.
Okay, what is actually the date when we say yesterday right so we can use the function.
Date, add right and then on in this record date, column right.
What we are going to do is basically add basic, add minus 20, basically, one day before right so enter well: minus 1 D and let's Alias this as Yesterdays date right, okay! Now the second thing is we.
We also need to find out that what is the previous date recorded in this table right so let's say we are going to use the lag window function, so lag it.
What it does is, basically you can tell how many rows before the current row.
You know you want to get the data for and for which column you want that, and it is going to get that right.
So, for example, I want to get one row before the current Row from the record date.
Column right so lag record date, and since this is a window function, you always need to write the Over clause for a window function and you order by record date right because you need to order by recorded in ascending order, so that you know you are going to get the previous ones and let's Alias this as uh previous record did right.
So now.
Why did we do two two things right, so why won't it work for on using just this one right, because here in this particular example, it will work but again, as I was mentioning if you take 6th of January 2015 right.
So what will it will find? Okay, so for 6th of January? What is the lag one, so it will find oh okay, nice 4th of January 2015, but that is not yesterday.
That is why we need to do these both right.
Okay, so once we have this and then finally, we need to find out the previous temperature right so similarly employ the same thing lag from the temperature column.
Since this is a window function, you write over order by record date in ascending order and let's release this as previous temperature right.
Okay, let me run this.
This is not yet complete, but let's see if we are getting what we are, you know trying to get okay, so we have okay, so for first one right so for one first of January 2015 the temperature is 10, and then what is the yesterday's date on first of January, it is going to be 31st of December 2015 right 31st of December, and then why is it null and null? Because there is nothing before this right.
So that is why one row before the current row, you have null urinal, but if you look at the second part right, so you have uh 2nd of January 2015.
The temperature is 25 the one day before this, that is yesterday's, is going to be first of January 2015.
uh.
This is 1st of January 2015, basically, the yesterday's record date right and what was the temperature right? So you get this now.
What we can do is we can store this in a comment table expression with CTE as right, and then what we need to do is from this Common Table expression only keep those rows where yesterday's date is equal to previous record day.
Why? Because it is making sure that okay in the table that you have, whether in the weather table that you have your previous or the day before just before today, is equal to the yesterday's date.
That is, it actually is yesterday and not some days.
You know some two three five ten days before today, right because we need to find out the IDS of due dates with higher temperature compared to previous dates right and once you make sure you know that the previous recorded in the weather table is actually yesterday's date, then you are also going to check that you know the temperature that the current temperature is greater than the previous temperature right.
So since we have already made sure using this line of code, that the previous record date in the weather table is indeed yesterday's date.
So then, when we are saying temperature greater than previous temperature, it means that we are checking for higher temperatures compared to its previous rates right.
So once we have this, then what do we need in the output? We just need the IDS right so return ID, and since we don't need to order this by anything, so this looks good.
Let me go ahead and run this to see what happens.
So if this is accepted, our output is same as expected, output.
Let me go ahead and submit it to see if it passes all the test cases.
So if this passes all the test cases- and this is how it again very simple question, but it is not as straightforward as you might think by just reading this question, it is not direct application of lag and you can do the same thing with lead as well.
You know let in the comment section how or what the solution will look like if you using lead instead of the Lang window function but yeah, you cannot do it only with lag or lead or or you might have to come up with some other logic.
You know which make sure that yesterday's date is indeed what you have in the weather table right so yeah.
This is how we do it.
Let me know in the comment section below, if you can think of a more better way or a more efficient way to solve this question.
Let the solution be in the comment section below and until then I will see you guys in the next video.