← 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;

← Back to the chapter · Previous detail