<!-- https://learnsql.cpetoday.com/analyze/smart-home -->
# IoT and smart home (smarthome)

`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`, database `smarthome`, tables in the `dbo` schema. See [Connect Excel and Power BI](/connect).
- **In the browser:** open [the database page](/databases/smarthome) 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 are `gap-filled`. Filter on `quality` before 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?**

```sql
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?**

```sql
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?**

```sql
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?**

```sql
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?**

```sql
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?**

```sql
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:

```sql
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.** `rooms` have `x_ft`, `y_ft`, `width_ft` and `depth_ft` and `devices` have `x_ft`, `y_ft` and `z_ft`. A scatter chart of `x_ft` against `y_ft`, sized by motion events, is a heat map of the house.
- **Trips.** `trips` have origin and destination coordinates, and `trip_points` have the GPS breadcrumbs. A map with latitude and longitude, ordered by `point_seq`, draws each route.
- **Fiscal year.** `fiscal_year`, `fiscal_quarter` and `fiscal_period` sit on `events`, `alerts`, `energy_hourly`, `device_energy_daily`, `weather_daily`, `utility_bills`, `ev_charging_sessions` and `trips`. See [maps and fiscal calendars](/analyze/maps-and-fiscal) for the shared pattern.

## Excel and Power BI

- **Star model.** Use `readings`, `events` and `energy_hourly` as fact tables, with `devices`, `rooms`, `people` and a date table as dimensions. `fiscal_calendar` is a ready-made date table.
- **Keep the big table light.** `readings` has 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.
