Exercise

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.

Instructions

100 XP
  • Calculate the minimum, the first, second (median), and third quartile, and the max heartbeat timestamp using ordered-set aggregate functions.