Description
Table: Weather
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| recordDate | date |
| temperature | int |
+---------------+---------+
id is the column with unique values for this table.
There are no different rows with the same recordDate.
This table contains information about the temperature on a certain day.
Write a solution to find all dates’ Id with higher temperatures compared to its previous dates (yesterday).
Return the result table in any order.
The result format is in the following example.
Examples:
Example 1:
Input:
Weather table:
+----+------------+-------------+
| id | recordDate | temperature |
+----+------------+-------------+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+----+------------+-------------+
Output:
+----+
| id |
+----+
| 2 |
| 4 |
+----+Solution in Pandas
Python
import pandas as pd
def rising_temperature(weather: pd.DataFrame) -> pd.DataFrame:
# Sort the DataFrame by 'recordDate' to ensure chronological order
weather = weather.sort_values(by='recordDate')
# Calculate the difference in days between the current and previous recordDate
weather['prev_date'] = weather['recordDate'].shift(1)
weather['date_diff'] = (weather['recordDate'] - weather['prev_date']).dt.days
# Create a shifted temperature column to represent the previous day's temperature
weather['prev_temperature'] = weather['temperature'].shift(1)
# Filter the rows where the current temperature is higher than the previous day's temperature
# and the date difference is exactly 1 day
higher_temp = weather[(weather['temperature'] > weather['prev_temperature']) & (weather['date_diff'] == 1)]
# Select and return only the 'id' column from the filtered DataFrame
result = higher_temp[['id']]
return resultExplanation:
- Date Difference Calculation:
weather['date_diff']calculates the difference in days between the currentrecordDateand the previousrecordDate. This ensures we are comparing only consecutive days. - Filtering: The filter now checks both that the temperature is higher and that the difference in dates is exactly one day (
weather['date_diff'] == 1).
Handling Edge Cases:
- If a day is missing in the data, the solution will not falsely compare non-consecutive dates, which addresses the issue seen in the test case.
Solution in MySQL
SQL
-- Step 1: Create a Common Table Expression (CTE) to find the previous day's temperature
WITH PreviousDay AS (
SELECT
w1.id AS current_id,
w1.temperature AS current_temp,
w2.temperature AS prev_temp
FROM
Weather w1
LEFT JOIN
Weather w2
ON
w1.recordDate = DATE_ADD(w2.recordDate, INTERVAL 1 DAY)
)
-- Step 2: Select records where the current day's temperature is higher than the previous day's temperature
SELECT
current_id AS id
FROM
PreviousDay
WHERE
prev_temp IS NOT NULL
AND current_temp > prev_temp;Solution in PostgreSQL
SQL
-- Step 1: Create a Common Table Expression (CTE) to find the temperature of the previous day
WITH PreviousDay AS (
SELECT
w1.id AS current_id, -- ID of the current day
w1.temperature AS current_temp, -- Temperature of the current day
w2.temperature AS prev_temp -- Temperature of the previous day
FROM
Weather w1
LEFT JOIN
Weather w2
ON
w1.recordDate = w2.recordDate + INTERVAL '1 day' -- Join on the previous day
)
-- Step 2: Select records where the current day's temperature is higher than the previous day's temperature
SELECT
current_id AS id -- Output the ID of the days with rising temperatures
FROM
PreviousDay
WHERE
prev_temp IS NOT NULL -- Ensure that there was a previous day (to handle edge cases)
AND current_temp > prev_temp; -- Select days with higher temperatures than the previous day