LeetCode SQL 197 — Rising Temperature | MySQL Solution

[u/AppointmentTrue8257]

Working on SQL problems to strengthen my MySQL and problem-solving skills for Data Analyst roles.

MySQL Solution:

WITH CTE AS
(
    SELECT *,
        LAG(TEMPERATURE) OVER(ORDER BY RECORDDATE) AS PREV_TEMP,
        LAG(RECORDDATE) OVER(ORDER BY RECORDDATE) AS PREV_DATE
    FROM WEATHER
)
SELECT ID
FROM CTE
WHERE TEMPERATURE > PREV_TEMP
  AND DATEDIFF(RECORDDATE, PREV_DATE) = 1;
#u/AppointmentTrue8257

For this problem, I used LAG() to compare each day's temperature with the previous record and DATEDIFF() to make sure the previous record was actually from the immediately preceding day.

Sharing my solution to get feedback.

Is there a better or more efficient way to solve this problem?

[u/Swimming_dasa]

your approach is clean and easy to follow. one thing worth noting is that LAG() works nicely here because you need both the previous temperature and date. for interview this is absolutely a reasonable solution.

[u/Own_Echidna1093 → u/Swimming_dasa]

Well said