Get startedGet started for free

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

View Course

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;
Edit and Run Code