EPA Temperature Monitoring
The Environmental Protection Agency (EPA) monitors the daily temperatures of different cities to study climate change and its impact. The agency believes that extreme fluctuations in temperature, such as a sudden rise after a day of fall, can have adverse environmental effects.
You are given a table city_temperatures, with the following columns:
date(date): The date of the recorded temperature.temperature(float): The temperature recorded on that date.
Write an SQL query to identify days when the temperature rose at least 5 degrees after falling at least 3 degrees. Return the date and the temperature of those days.
We make use of a CTE named previous_temperatures to produce a set of records which include the current date's temperature, the previous day's temperature, and the temperature of the day before the previous day using the LAG window function.
LAG(temperature, 1)returns the temperature from 1 day before (the immediate previous row when ordered by date).LAG(temperature, 2)returns the temperature from 2 days before.
From this table, we can define two conditions to check for using WHERE and AND i.e. check that current day’s temperature has risen 5 units from previous day, and that previous day’s temperature has fallen 3 units from 2 days ago.
WITH previous_temperatures AS (
SELECT
date,
temperature,
LAG(temperature, 1) OVER (ORDER BY date) AS prev_temperature,
LAG(temperature, 2) OVER (ORDER BY date) AS prev_prev_temperature
FROM
city_temperatures
)
SELECT
date,
temperature
FROM
previous_temperatures
WHERE
temperature - prev_temperature >= 5 AND prev_prev_temperature - prev_temperature >= 3;
this task is misleading . i used lag(1) and lead(1) cuz it did not say "compare temperature from 2 days before and 1 day before" , it reads to me as if its asking "compare cur temperature to prev and future and see if it rose and fall"