IoT and smart home (smarthome)
One connected family home: 83 devices, 13 months of sensor readings, solar, battery and EV energy, automations, security, bills, trips and a floor plan.
Last updated
View as Markdown For AI agents All guides as text
On this page
You'll be able to
- Connect to the smarthome database and describe how devices, readings and bills join.
- Analyze solar, battery and EV energy with hourly and monthly queries.
- Find an outage and a water leak in the sensor readings.
- Map devices to rooms and floors, and group results by fiscal period.
smarthome is the data of one connected family home, the Rivera household in Bellevue, WA: two adults, a teen, a child and a dog, with 83 devices in 12 rooms, rooftop solar, a home battery and an electric car. It covers 24 September 2025 to 24 October 2026 and holds about 1.9 million rows in 39 tables, identical on SQL Server and MariaDB.
It is built for personal household data: sensor time series, events, energy, bills, weather, security and routines, with a geospatial layer (the home, the places the family drives to, GPS trips and a floor plan) and a fiscal calendar (the October to September "water year" that utilities use).
Connect to it
- SQL Server: server
learnsql.cpetoday.com,1433, databasesmarthome, tables in thedboschema. See Connect Excel and Power BI. - In the browser: open the database page for the Playground, the SQL editor and the schema explorer.
- Time: every timestamp is the home's local standard time all year, so there are no daylight-saving gaps or repeats. The as-of time is 2026-10-24 12:00.
Everything is connected
The data is generated so that the numbers agree with each other:
- Weather drives the house. Outdoor temperature runs the heat pump (heating through winter, cooling in the July heat wave) and sunlight drives the solar array.
- People drive the rest. Who is home, and in which room, determines lights, motion, CO2, humidity, hot-water use and appliance cycles.
- Energy reconciles. Grid import plus solar plus battery discharge equals grid export plus household load plus battery charge. The hourly meters are integrated from the 15-minute power channels.
- Bills are priced from the meters. The electric bill applies the time-of-use rates to the hourly import and export, and the gas and water bills come from the gas and water meters.
Tables at a glance
| Area | Tables | What they hold |
|---|---|---|
| Home | homes, rooms, people |
One home with a full address, 12 rooms with floor-plan rectangles, and five household members (the dog included) |
| Devices | device_types, device_models, devices, sensor_channels, energy_meters |
83 devices (thermostats, sensors, lights, locks, cameras, appliances, energy gear), their models and positions, 79 sensor channels, 8 energy meters |
| Readings and events | readings, events, commands |
1.6 million readings every 15 to 60 minutes, 153,000 events (motion, doors, locks, lights, appliance cycles), 10,800 commands |
| Automations | automations, automation_runs |
17 automations and 6,200 runs with their outcomes |
| Alerts and upkeep | alerts, firmware_updates, maintenance_log |
98 alerts, 146 firmware updates, battery and filter changes |
| Energy and bills | energy_hourly, device_energy_daily, ev_charging_sessions, rate_plans, utility_bills |
Hourly meters, daily energy per appliance, 167 EV sessions, time-of-use rate plans, electric, gas, water and internet bills |
| Weather | weather_hourly, weather_daily |
Hourly and daily weather with sunrise and sunset |
| People | presence_log, routines, sleep_sessions, access_codes |
Home and away history, routines, 1,500 nights of sleep with the bedroom's temperature and CO2, door codes for family, guests and services |
| Security | camera_clips, alarm_sessions |
3,800 camera clips and 580 alarm sessions |
| Places and trips | points_of_interest, geofences, trips, trip_points |
11 places, 7 geofences, 704 EV trips and their GPS breadcrumbs |
| Reference | geo_states, geo_places, geo_postal_codes, calendar_dates, fiscal_calendar, dataset_info |
Geography and calendars |
How the tables join
homes ──< rooms ──< devices >── device_models >── device_types
│
├──< sensor_channels ──< readings (the big time series)
├──< events (motion, doors, locks, lights, cycles)
├──< commands >── automation_runs >── automations
├──< alerts, firmware_updates, maintenance_log
└──< camera_clips, alarm_sessions, ev_charging_sessions
energy_meters ──< energy_hourly devices ──< device_energy_daily
people ──< presence_log, sleep_sessions, routines people ──< trips ──< trip_points
points_of_interest ──< geofences fiscal_calendar / calendar_dates join on the date
Rules built into the data
- Readings have a quality flag. About 1% of values are missing, about 0.3% are
suspect(spikes, zeros and-999), and some aregap-filled. Filter onqualitybefore you average. - Different channels have different rhythms. Most channels report every 15 minutes, four report every 30, soil moisture and gas every hour, and battery levels once a day (see
sensor_channels.sample_interval_s). - Grid power is signed. In
grid_power_w, positive means buying from the utility and negative means selling to it. - Fiscal year. The fiscal year is October to September, named by the year it ends, so FY2026 is October 2025 to September 2026 and fiscal period 1 is October.
- Rate change. On 1 April 2026 the time-of-use rates rise and the export credit falls (see
rate_plans).
Things that happen in the data
- A heat wave in late July and early August 2026, a cold snap in mid-January 2026, and wildfire smoke on 10 to 13 September 2026.
- A windstorm outage on 14 January 2026 (03:10 to 09:40): the battery carries the house and the freezer warms to 14F.
- A laundry-room leak on 17 February 2026: the automatic shutoff fails because the valve was offline, and Jordan closes it by hand 29 minutes later.
- A thermostat offline for two days in March, batteries that run low and are replaced, and a few firmware updates that fail.
- A family vacation from 10 to 24 July 2026 with the house empty, a Thanksgiving trip, grandparents at Christmas, and three business trips.
Questions to try
How much of the house's electricity comes from the sun, month by month?
SELECT fiscal_period, ROUND(SUM(consumption_kwh), 0) AS used_kwh, ROUND(SUM(solar_kwh), 0) AS solar_kwh,
ROUND(SUM(grid_import_kwh), 0) AS imported_kwh, ROUND(SUM(grid_export_kwh), 0) AS exported_kwh,
ROUND((1 - SUM(grid_import_kwh) / SUM(consumption_kwh)) * 100, 1) AS self_sufficiency_pct
FROM vw_daily_energy
WHERE fiscal_year = 2026
GROUP BY fiscal_period
ORDER BY fiscal_period;
Self-sufficiency is about 6% in January (fiscal period 4) and about 60% in June and July (periods 9 and 10).
What did each utility cost in fiscal 2026?
SELECT utility_type, COUNT(*) AS bills, ROUND(SUM(amount_due), 2) AS total_due FROM utility_bills WHERE fiscal_year = 2026 GROUP BY utility_type ORDER BY total_due DESC;
Which rooms were warmest in July?
SELECT room_name, ROUND(AVG(avg_value), 1) AS avg_temp_f, ROUND(MIN(min_value), 1) AS coldest, ROUND(MAX(max_value), 1) AS warmest FROM vw_room_comfort_daily WHERE metric = 'temperature_f' AND reading_date >= '2026-07-01' AND reading_date < '2026-08-01' GROUP BY room_name ORDER BY avg_temp_f DESC;
Does stuffy bedroom air hurt sleep?
SELECT CASE WHEN avg_bedroom_co2_ppm < 800 THEN 'under 800 ppm'
WHEN avg_bedroom_co2_ppm < 1100 THEN '800-1,100 ppm' ELSE 'over 1,100 ppm' END AS co2_band,
COUNT(*) AS nights, ROUND(AVG(sleep_score * 1.0), 1) AS avg_sleep_score
FROM sleep_sessions
GROUP BY CASE WHEN avg_bedroom_co2_ppm < 800 THEN 'under 800 ppm'
WHEN avg_bedroom_co2_ppm < 1100 THEN '800-1,100 ppm' ELSE 'over 1,100 ppm' END
ORDER BY avg_sleep_score DESC;
Nights with the highest CO2 score lower than nights with fresh air.
How far did the EV go, and for what?
SELECT purpose, COUNT(*) AS trips, ROUND(SUM(miles), 0) AS miles, ROUND(SUM(kwh_used), 0) AS kwh FROM trips WHERE fiscal_year = 2026 GROUP BY purpose ORDER BY miles DESC;
Which alerts come up most?
SELECT alert_type, severity, COUNT(*) AS alerts FROM alerts GROUP BY alert_type, severity ORDER BY alerts DESC OFFSET 0 ROWS FETCH FIRST 6 ROWS ONLY;
Where is the motion? Count motion events by room from the room sensors:
SELECT r.room_name, COUNT(*) AS motion_events FROM events e JOIN devices d ON d.device_id = e.device_id JOIN rooms r ON r.room_id = d.room_id WHERE e.event_type = 'motion' AND e.fiscal_year = 2026 AND d.device_name LIKE 'Room Sensor%' GROUP BY r.room_name ORDER BY motion_events DESC OFFSET 0 ROWS FETCH FIRST 5 ROWS ONLY;
All of these run on SQL Server and MariaDB. More ideas: the outage hour by hour (readings for battery_soc_pct and grid_power_w), the leak minute by minute, empty-house energy from presence_log, battery health from the battery_pct channels, appliance cost per cycle (device_energy_daily with the rate plan), and a floor-plan heat map.
Maps, floor plans and fiscal periods
- Floor plan.
roomshavex_ft,y_ft,width_ftanddepth_ftanddeviceshavex_ft,y_ftandz_ft. A scatter chart ofx_ftagainsty_ft, sized by motion events, is a heat map of the house. - Trips.
tripshave origin and destination coordinates, andtrip_pointshave the GPS breadcrumbs. A map with latitude and longitude, ordered bypoint_seq, draws each route. - Fiscal year.
fiscal_year,fiscal_quarterandfiscal_periodsit onevents,alerts,energy_hourly,device_energy_daily,weather_daily,utility_bills,ev_charging_sessionsandtrips. See maps and fiscal calendars for the shared pattern.
Excel and Power BI
- Star model. Use
readings,eventsandenergy_hourlyas fact tables, withdevices,rooms,peopleand a date table as dimensions.fiscal_calendaris a ready-made date table. - Keep the big table light.
readingshas 1.6 million rows. Load it filtered to the channels and months you need, or use the daily views. - Measures to write. Energy used, Solar produced, Self-sufficiency %, Net grid kWh, Cost per kWh, Average room temperature, Comfort hours, Automation success %.
Realistic imperfections
The data has the usual smart-home mess: missing and suspect readings, devices that drop off the network, failed automations and firmware updates, low batteries, duplicate-looking alerts, and a few sensors that are wrong in the same way for a day. It is clean enough to reconcile, and messy enough to need filtering.
Try it on the data
Open the smarthome database page, browse it in the Playground, write your own queries in the SQL editor, see how the tables connect in the schema explorer, or ask an AI assistant.