Home Assistant Device tracker state duration aggregation for SQLite

device_tracker_agg.sql

WITH p AS (
	WITH q AS (
		WITH y AS (
			WITH x as (
				SELECT 
					sm.entity_id,
					s.state,
					s.last_updated_ts,
					COALESCE(LAG(s.state) OVER (partition by sm.entity_id ORDER BY last_updated_ts ASC), s.state) as last_state
				FROM states s
				INNER JOIN states_meta sm ON s.metadata_id = sm.metadata_id  
				WHERE sm.entity_id LIKE 'device_tracker.%' AND s.last_updated_ts >= (strftime('%s', 'now') - (30 * 24 * 60 * 60))
			)
			SELECT 
				x.entity_id,
				x.state,
				x.last_updated_ts,
				SUM(x.state <> x.last_state) OVER (partition by x.entity_id order by x.last_updated_ts) grp
			FROM x
			)
		SELECT 
			y.entity_id, 
			y.state, 
			grp,
			min(y.last_updated_ts) time_from,
			max(y.last_updated_ts) time_to,
			count() as cnt
		FROM y
		GROUP BY y.entity_id, y.state, y.grp
		ORDER BY time_from
	)
	SELECT 
		q.entity_id,
		q.state,
		COALESCE(LEAD(q.time_from) OVER (partition by q.entity_id ORDER BY q.time_from), unixepoch()) - q.time_from as diff,
		q.cnt
	FROM q
)
SELECT 
	p.entity_id,
	p.state, ROUND(sum(p.diff) / 60 / 60, 3) as hours, count() as count
FROM p
GROUP BY p.entity_id, p.state
ORDER BY  p.entity_id, p.state