37. Warmer Than the Previous Day
MediumJoinsself-joinnon-equi joindate arithmetic
A self-join lets you compare a row to another row in the same table — here, today's weather versus yesterday's.
Task: find every date whose temperature was strictly higher than the temperature on the immediately preceding calendar day. Return just the recorded_on date.
Match "the previous day" with date arithmetic: date(today.recorded_on, '-1 day') equals yesterday's date. (The first day has no predecessor, so it can never qualify.)
Output column: recorded_on. Order by recorded_on ascending.
Tables
Loading schema…
Expected outputrows must come back in this order
Computing expected output…
This is the result for the example data above. On Submit your query is graded against this example plus 4 hidden edge cases — 5test cases in all. A sloppy query that only fits the example won't pass.
SQL· SQLite · runs in your browser
⌘/Ctrl+↵ run · ⇧⌘/Ctrl+↵ submit
Run a query to see its output here.