/* Based on device data, this query returns the average * of the battery level for every hour for each device_id */ WITH avg_metrics AS ( SELECT device_id, DATE_BIN('1 hour'::INTERVAL, time, 0) AS period, AVG(battery_level) AS avg_battery_level FROM devices.readings GROUP BY 1, 2 ORDER BY 1, 2 ) SELECT period, t.device_id, manufacturer, avg_battery_level FROM avg_metrics t, devices.info i WHERE t.
![CrateDB – The Hyper-Fast Database that Truly Scales](https://cdn-ak-scissors.b.st-hatena.com/image/square/d24df471a80d7b5c3d29d3917766e00eb7724b9f/height=288;version=1;width=512/https%3A%2F%2Fcrate.io%2Fwp-content%2Fuploads%2F2017%2F05%2Fcratedb-goat.jpg)