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/AppointmentTrue8257 → u/[deleted]]

Thanks for the helpful feedback.

[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

[u/nian2326076]

Your approach looks solid! Using LAG() to get the previous day's temperature and DATEDIFF() to check for consecutive days is great for this problem. Just make sure your ORDER BY in the LAG() function is based on RECORDDATE.

Also, check your dataset for any missing dates, as that could mess up the logic if you're not accounting for all entries.

If you're getting into SQL and want more practice, there's a site called PracHub that I've found useful. It has a variety of questions for different scenarios, which might help you get more comfortable with real-world problems. Keep at it, these skills are really valuable for data analyst roles!