Given a dataset of smart meter readings on Kraken, write a query to find the peak energy consumption hour for each household over a 30-day period.
Use the supplied 30-day period from 2025-01-01 inclusive through 2025-01-31 exclusive. Include only households with at least one non-null reading in that period. If multiple hours tie, return the earliest hour.
household_idhousehold_id, household_name, peak_hour, and total_consumption_kwhpeak_hour is the hour of day from 0 through 23, and total_consumption_kwh is that household's summed consumption during the peak hour| Column | Type | Description |
|---|---|---|
| household_idPK | INTEGER | Unique household identifier |
| household_name | VARCHAR(100) | Display name for the household |
| Column | Type | Description |
|---|---|---|
| reading_idPK | INTEGER | Unique smart meter reading identifier |
| household_id | INTEGER | Household associated with the reading |
| reading_at | TIMESTAMP | Timestamp when the reading was recorded |
| consumption_kwh | NUMERIC(10,2) | Energy consumed in kilowatt-hours |