Q: Collapse rows into buckets in PostgreSQL I have the following dataset in PostgreSQL:
row_id, wind_speed_category, timestamp
1, 3, 2021-01-20 10:00:01
2, 3, 2021-01-21 11:00:01
3, 3, 2021-01-21 12:00:01
4, 4, 2021-01-21 14:00:01
5, 4, 2021-01-23 10:00:01
6, 3, 2021-02-22 10:00:01

I would like to collapse the rows into "running" buckets, so getting a result somewhat like:
wind_speed_category, interval_seconds
3, 86400
4, 2764800
3, 0

The interval_seconds is based upon the interval of timestamp in first row in the bucket and last row + 1 in bucket.
I've managed to get this far: http://sqlfiddle.com/#!17/3c55e/1
But don't know how to collapse into buckets/partitions/windows.

A: This is a gaps and islands problem, and the first thing needed to solve it is to order all records ascending or descending by timestamp, across the entire table.  Once this is done, we can try the following:
WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY timestamp) rn1,
              ROW_NUMBER() OVER (PARTITION BY wind_speed_category
                                 ORDER BY timestamp) rn2
    FROM yourTable
)

SELECT wind_speed_category,
       EXTRACT(EPOCH FROM (MAX(timestamp) - MIN(timestamp))) AS interval_seconds
FROM cte
GROUP BY wind_speed_category, rn1-rn2;


Demo
