Rising Temperature
Asked at Meta
Problem
Write a SQL query to find all dates where the temperature was higher than the previous day. The table Weather has columns id (int), recordDate (date), and temperature (int). Return the result with column id.
Asked At
| Company | Difficulty | |
|---|---|---|
| Meta | Easy | View all Meta questions → |
How to Think About It
Self-join the table on itself, matching each record to the one from the day before.
Use DATE_SUB or interval arithmetic to find records exactly one day apart.
Compare temperatures: w1.temperature > w2.temperature where w2 is the previous day.
Return w1.id for all matching rows.
Alternatively, use window functions with LAG() to access the previous row's temperature.
Optimal Approach
Self-join the Weather table: join w1 with w2 where w2.recordDate = DATE_SUB(w1.recordDate, INTERVAL 1 DAY). Filter rows where w1.temperature > w2.temperature. Return w1.id. This handles the one-day gap requirement and works with MySQL date functions.
What Trips People Up in Real Interviews
Dates are guaranteed to be unique, so a simple join on DATE_SUB(recordDate, INTERVAL 1 DAY) works.
Using LAG() is cleaner but requires knowledge of window functions.
Make sure to use the correct date arithmetic syntax for your SQL dialect.
Ask whether the output column should be named id or temperature.
Corner case: if there is no previous day in the table, that row is simply excluded.
Solution Code
SELECT w1.id FROM Weather w1 JOIN Weather w2 ON w2.recordDate = DATE_SUB(w1.recordDate, INTERVAL 1 DAY) WHERE w1.temperature > w2.temperature;
Frequently Asked Questions
What is the Rising Temperature problem?
Write a SQL query to find all dates where the temperature was higher than the previous day. The table Weather has columns id (int), recordDate (date), and temperature (int). Return the result with column id.
How do you solve Rising Temperature?
The optimal approach is described in detail above, including step-by-step walkthroughs, complexity analysis, and solution code in Python. Scroll up to the "Optimal Approach" section.
What companies ask Rising Temperature?
Rising Temperature is asked at Meta. It is a easy difficulty problem.
What are common mistakes on Rising Temperature?
- Dates are guaranteed to be unique, so a simple join on DATE_SUB(recordDate, INTERVAL 1 DAY) works.
- Using LAG() is cleaner but requires knowledge of window functions.
- Make sure to use the correct date arithmetic syntax for your SQL dialect.
- Ask whether the output column should be named id or temperature.
- Corner case: if there is no previous day in the table, that row is simply excluded.