Apache Druid

Screenshot 2024-01-09 at 13.33.05.png

Hey <@U064L9Q9Y3A> I _think_ you want to use a Window function, something like this:
```SELECT
    DATE_FORMAT(timestamp, '%Y-%m-%d %H:00:00') AS hour,
    value - LAG(value) OVER (ORDER BY DATE_FORMAT(timestamp, '%Y-%m-%d %H:00:00')) AS consumption
FROM (
    SELECT
        timestamp,
        FIRST_VALUE(value) OVER (PARTITION BY DATE_FORMAT(timestamp, '%Y-%m-%d %H') ORDER BY timestamp) AS value
    FROM readings
) AS first_readings
ORDER BY hour;```


• The inner query uses `FIRST_VALUE` to get the first reading (`value`) of each hour. It partitions the data by hour (formatted as `'%Y-%m-%d %H'`) and orders by `timestamp` within each partition.
• The outer query then calculates the `consumption` as the difference between the value of the current hour and the previous hour using the `LAG` window function.
• `DATE_FORMAT(timestamp, '%Y-%m-%d %H:00:00')` formats the timestamp to represent each hour.
