This SQL query calculates the number of times doors and windows have been opened and closed in Home Assistant, along with the average uses per day, and retrieves the current voltage for each sensor.
To be used with the SQLite Web addon, replace all binary_sensor and sensor entities with your own.
WITH state_changes AS
(SELECT sm.entity_id,
s.state,
LAG(s.state) OVER (PARTITION BY sm.entity_id
ORDER BY s.last_updated) AS prev_state
FROM states s
JOIN states_meta sm ON s.metadata_id = sm.metadata_id
WHERE sm.entity_id IN ('binary_sensor.buanderie_fenetre_contact',
'binary_sensor.chambre_fenetre_bureau_contact',
'binary_sensor.chambre_fenetre_lit_contact',
'binary_sensor.cuisine_fenetre_contact',
'binary_sensor.salon_fenetre_contact',
'binary_sensor.salon_porte_contact')
AND s.state IN ('on',
'off'))
SELECT CASE sc.entity_id
WHEN 'binary_sensor.buanderie_fenetre_contact' THEN 'Fenêtre - Buanderie'
WHEN 'binary_sensor.chambre_fenetre_bureau_contact' THEN 'Fenêtre - Bureau'
WHEN 'binary_sensor.chambre_fenetre_lit_contact' THEN 'Fenêtre - Chambre'
WHEN 'binary_sensor.cuisine_fenetre_contact' THEN 'Fenêtre - Cuisine'
WHEN 'binary_sensor.salon_fenetre_contact' THEN 'Fenêtre - Salon'
WHEN 'binary_sensor.salon_porte_contact' THEN 'Porte - Salon'
END AS entity_name,
COUNT(*) AS total_transitions,
SUM(CASE
WHEN state = 'on' THEN 1
ELSE 0
END) AS openings,
SUM(CASE
WHEN state = 'off' THEN 1
ELSE 0
END) AS closings,
ROUND((COUNT(*) / 2.0) / (julianday('now') - julianday('2025-05-28') + 1), 2) AS avg_uses_per_day,
CASE sc.entity_id
WHEN 'binary_sensor.buanderie_fenetre_contact' THEN
(SELECT s.state
FROM states s
JOIN states_meta sm ON s.metadata_id = sm.metadata_id
WHERE sm.entity_id = 'sensor.buanderie_fenetre_voltage'
ORDER BY s.state_id DESC
LIMIT 1)
WHEN 'binary_sensor.chambre_fenetre_bureau_contact' THEN
(SELECT s.state
FROM states s
JOIN states_meta sm ON s.metadata_id = sm.metadata_id
WHERE sm.entity_id = 'sensor.chambre_fenetre_bureau_voltage'
ORDER BY s.state_id DESC
LIMIT 1)
WHEN 'binary_sensor.chambre_fenetre_lit_contact' THEN
(SELECT s.state
FROM states s
JOIN states_meta sm ON s.metadata_id = sm.metadata_id
WHERE sm.entity_id = 'sensor.chambre_fenetre_lit_voltage'
ORDER BY s.state_id DESC
LIMIT 1)
WHEN 'binary_sensor.cuisine_fenetre_contact' THEN
(SELECT s.state
FROM states s
JOIN states_meta sm ON s.metadata_id = sm.metadata_id
WHERE sm.entity_id = 'sensor.cuisine_fenetre_voltage'
ORDER BY s.state_id DESC
LIMIT 1)
WHEN 'binary_sensor.salon_fenetre_contact' THEN
(SELECT s.state
FROM states s
JOIN states_meta sm ON s.metadata_id = sm.metadata_id
WHERE sm.entity_id = 'sensor.salon_fenetre_voltage'
ORDER BY s.state_id DESC
LIMIT 1)
WHEN 'binary_sensor.salon_porte_contact' THEN
(SELECT s.state
FROM states s
JOIN states_meta sm ON s.metadata_id = sm.metadata_id
WHERE sm.entity_id = 'sensor.salon_porte_voltage'
ORDER BY s.state_id DESC
LIMIT 1)
END AS current_voltage_mV
FROM state_changes sc
WHERE state <> prev_state
GROUP BY sc.entity_id
ORDER BY entity_name;