Sensor data analysis
You are a developer on a team creating sensor collection data infrastructure for a smart home network. You are analyzing a health check table called heartbeat_timestamps. This table contains seven days worth of "heartbeat" data logged by sensors on a network containing several devices.
Sensors come online and drop off independently. Once a sensor starts to log its heartbeat, it reports at regularly spaced intervals thereafter until it goes offline.
Sensors often fail to report a heartbeat in between coming online and going offline. You seek to better understand the distribution of heartbeats over time by binning the data. For the purpose of this analysis, the table is assumed to contain a single time series.
This exercise is part of the course
Time Series Analysis in PostgreSQL
Exercise instructions
- Calculate the minimum, the first, second (median), and third quartile, and the max heartbeat timestamp using ordered-set aggregate functions.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Calculate the min, max and 3 quartiles
SELECT
___ as min,
___ AS quartile_1,
___ AS median,
___ AS quartile_2,
___ AS max
FROM heartbeat_timestamps;