Day two of Refresher Week, and our focus today was SQL. I'll admit it's been a month since I last did any SQL, but I was hopeful I could pull through the session...this was the case until I hit a roadblock!
We spent the day working through the leetcode SQL exercises - you can have a go here.
The roadblock in question was the following exercise:
My initial thought process was the following:
- create a duplicate Weather table (let's call it table B)
- use the DATEADD function to generate dates that are 1 day before in the recordDate field for table B
- join table B to the original Weather table (i.e. the Input) on the recordDate field
Linking each recordDate from the Input table to the recordDate from table B will then allow you to compare the temperature in both tables and write a condition to return all IDs where temperature from the Input table is greater than the temperature from table B, i.e. where the temperature for any given date is greater than the day before, right?..Well so I thought.
When I ran the query, the output generated was ID 2 only, but we are told that the output should be IDs 2 and 4.
This is where the power of sketching comes into play.
The approach I had taken is visualised below:
The above sketch allowed me to visualise what my query was doing and why it was generating the output of ID 2.
After this, it became clear where I was going wrong and what I needed to change in my query to generate ID 4 in the output, along with 2. Instead of using DATEADD to generate -1 days from recordDate in table B, I simply needed to change this to DATEADD 1 day.
The below sketch visualises the process and output of using DATEADD to add a day instead of subtracting a day from recordDate in table B.
I'll admit I was this close to calling it quits on this exercise because the first method made logical sense to me, and I couldn't understand why my output wasn't matching the 'correct' output. However, after the above sketch I feel fulfilled and accomplished that I didn't.
For completeness, the below shows the full query I used to generate the output - happy coding!
