I have a table with status and date for every day and I'm trying to find out when the statuses change and if there's gaps within each status change / how many days were of a certain status.
+------+----------------+--------+
| user | date | status |
+----- +----------------+--------+
| 1 | 12/01/2017 | open |
| 1 | 12/02/2017 | open |
| 1 | 12/03/2017 | open |
| 1 | 12/04/2017 | closed |
| 1 | 12/05/2017 | closed |
| 1 | 12/06/2017 | open |
| 1 | 12/07/2017 | open |
+------+----------------+--------+
Expected output:
+------+------------+----------------+-------- ----+------------+
| user | status | days_in_status | min | max |
+----- +------------+----------------+-------------+------------+
| 1 | open | 3 | 12/01/2017 | 12/03/2017 |
| 1 | closed | 2 | 12/04/2017 | 12/05/2017 |
| 1 | open | 2 | 12/06/2017 | 12/07/2017 |
+------+------ -----+----------------+-------------+-- ---------+
This is a type of gaps-and-islands problem. In this case, subtracting a sequential number from each day is probably the simplest solution for identifying the "islands":
select user, status, count(*) as num_days, min(date), max(date)
from (select t.*,
row_number() over (partition by user, status order by date) as seqnum
from t
) t
group by user, status, date - seqnum * interval '1 day'