Longest Steady Thermostat Streak

Problem

A smart-home service records every time a household's thermostat is set in the table `thermostat_log`, with the `home_id`, the `set_date`, and the `mode` chosen ('heat', 'cool', 'eco', or 'off'). A household is *steady* over a run of days when, for at least 5 consecutive calendar days, it set the thermostat exactly once per day AND chose the same mode on every one of those days. For each household that has at least one such run, return its single longest steady run: the `home_id`, the `mode` held, the run length in days as `run_length`, and the run's `start_date` and `end_date`. If a household has two runs of equal length, keep the earlier-starting one. Order the result by `run_length` descending, then by `home_id` ascending.

Tables

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 2 rows with the columns home_id, mode, run_length, start_date, end_date.

Starter code (SQL)

SELECT *
FROM thermostat_log;

Related SQL questions

• 3-Consecutive-Week Marketing StreakHard
• 30-Day Rolling Average Trading VolumeHard
• 7-Day Active User Rolling MetricHard
• 7-Day Revenue Moving AverageHard
• Always Mid-Pack BakersHard
• Annual Energy Output Growth by Panel ModelHard