By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE tbl ("user" int, date date, status text);
INSERT INTO tbl VALUES
(1, '2017-12-01', 'open')
, (1, '2017-12-02', 'open')
, (1, '2017-12-03', 'open')
, (1, '2017-12-04', 'closed')
, (1, '2017-12-05', 'closed')
, (1, '2017-12-06', 'open')
, (1, '2017-12-07', 'open')
;
CREATE TABLE
INSERT 0 7
SELECT "user", status
, count(*) AS days_in_status, min(date) AS min, max(date) AS max
FROM (
SELECT "user", status, date
, date - row_number() OVER (PARTITION BY "user", status ORDER BY date)::int AS grp_date
FROM tbl
) t
GROUP BY "user", status, grp_date
ORDER BY "user", min;
| user | status | days_in_status | min | max |
|---|---|---|---|---|
| 1 | open | 3 | 2017-12-01 | 2017-12-03 |
| 1 | closed | 2 | 2017-12-04 | 2017-12-05 |
| 1 | open | 2 | 2017-12-06 | 2017-12-07 |
SELECT 3