197 Rising Temperature

To determine if tomorrow’s temperature is higher than today’s, we can use the special date function DATE_ADD(). The key idea is to self-join the Weather table in a way that ensures we only retain rows where both dates exist.

Approach

1. Use a self-join:
   • We join Weather with itself on the condition that w1.recordDate is exactly one day ahead of w2.recordDate:

   FROM Weather AS w1
   JOIN Weather AS w2
   ON w1.recordDate = DATE_ADD(w2.recordDate, INTERVAL 1 DAY)
   

   • This ensures that for each date in w1, there exists a corresponding previous day in w2, keeping only consecutive date pairs.
2. Filter out cases where tomorrow’s temperature is higher:
   • We apply a WHERE condition to select records where the next day’s temperature is greater than the current day’s:

   WHERE w1.temperature > w2.temperature
   

Conclusion

By structuring the query this way, we can efficiently find days where the temperature increased compared to the previous day. If this logic is unclear, try manually constructing a small table to visualize the self-join and see how only rows where both dates exist are retained.