← Back to the chapter · Previous detail
Technical detail · Data Quality Monitoring
Query quality outcomes by device
This query groups observations received during the last 24 hours by device. It counts flagged and quarantined records as well as late-arrival and duplicate-retry flags, then ranks quarantine and flag counts so repeatedly failing devices are visible.
SELECT
device_id,
count(*) AS records_seen,
count(*) FILTER (WHERE quality_state = 'flagged') AS flagged_records,
count(*) FILTER (WHERE quality_state = 'quarantined') AS quarantined_records,
count(*) FILTER (WHERE quality_flags ? 'late_arrival') AS late_records,
count(*) FILTER (WHERE quality_flags ? 'duplicate_retry') AS duplicate_records
FROM telemetry_observation
WHERE receive_time >= now() - interval '24 hours'
GROUP BY device_id
ORDER BY quarantined_records DESC, flagged_records DESC;