SQL query to find gaps within a column of dates

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 |
+------+------ -----+----------------+-------------+-- ---------+

Answer

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'