Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mydata (
  person_id INTEGER,
  contact_day DATE,
  days_last_contact INTEGER
);

INSERT INTO mydata VALUES
  (1, '2015-02-09', NULL),
  (1, '2015-05-01', 81),
  (1, '2015-05-02', 1),
  (1, '2015-05-03', 1),
  (1, '2015-06-01', 29),
  (1, '2015-08-01', 61),
  (1, '2015-08-04', 3),
  (1, '2015-09-01', 28),

  (2, '2015-05-01', NULL),
  (2, '2015-06-01', 31),
  (2, '2015-07-01', 30),

  (3, '2015-05-01', NULL),
  (3, '2015-05-02', 1),
  (3, '2015-05-04', 2)
;

CREATE TABLE



INSERT 0 14


SELECT person_id, contact_day
     , count(*) FILTER (WHERE gap > 3) OVER (PARTITION BY person_id ORDER BY contact_day) AS dash_group
FROM  (
   SELECT person_id, contact_day
        , contact_day - lag(contact_day) OVER (PARTITION BY person_id ORDER BY contact_day) AS gap
   FROM   mydata
   ) sub
ORDER  BY person_id, contact_day;  -- optional


person_id
contact_day
dash_group


1
2015-02-09
0

1
2015-05-01
1

1
2015-05-02
1

1
2015-05-03
1

1
2015-06-01
2

1
2015-08-01
3

1
2015-08-04
3

1
2015-09-01
4

2
2015-05-01
0

2
2015-06-01
1

2
2015-07-01
2

3
2015-05-01
0

3
2015-05-02
0

3
2015-05-04
0



SELECT 14


-- Or (same performance):
SELECT person_id, contact_day
     , count(*) FILTER (WHERE step) OVER (PARTITION BY person_id ORDER BY contact_day) AS dash_group
FROM  (
   SELECT person_id, contact_day
        , lag(contact_day) OVER (PARTITION BY person_id ORDER BY contact_day) < contact_day - 3 AS step
   FROM   mydata
   ) sub
ORDER  BY person_id, contact_day;  -- optional


person_id
contact_day
dash_group


1
2015-02-09
0

1
2015-05-01
1

1
2015-05-02
1

1
2015-05-03
1

1
2015-06-01
2

1
2015-08-01
3

1
2015-08-04
3

1
2015-09-01
4

2
2015-05-01
0

2
2015-06-01
1

2
2015-07-01
2

3
2015-05-01
0

3
2015-05-02
0

3
2015-05-04
0



SELECT 14


-- variants for the same, 1-based
SELECT person_id, contact_day
     , count(*) FILTER (WHERE gap > 3) OVER (PARTITION BY person_id ORDER BY contact_day) + 1          AS dash_group1
     , count(*) FILTER (WHERE gap > 3 IS NOT FALSE) OVER (PARTITION BY person_id ORDER BY contact_day) AS dash_group2
     , count(*) FILTER (WHERE step) OVER (PARTITION BY person_id ORDER BY contact_day) + 1             AS dash_group3
     , count(*) FILTER (WHERE step IS NOT FALSE) OVER (PARTITION BY person_id ORDER BY contact_day)    AS dash_group4
FROM  (
   SELECT person_id, contact_day
        , contact_day - lag(contact_day) OVER (PARTITION BY person_id ORDER BY contact_day) AS gap
        , lag(contact_day) OVER (PARTITION BY person_id ORDER BY contact_day) < contact_day - 3 AS step
   FROM   mydata
   ) sub
ORDER  BY person_id, contact_day;  -- optional


person_id
contact_day
dash_group1
dash_group2
dash_group3
dash_group4


1
2015-02-09
1
1
1
1

1
2015-05-01
2
2
2
2

1
2015-05-02
2
2
2
2

1
2015-05-03
2
2
2
2

1
2015-06-01
3
3
3
3

1
2015-08-01
4
4
4
4

1
2015-08-04
4
4
4
4

1
2015-09-01
5
5
5
5

2
2015-05-01
1
1
1
1

2
2015-06-01
2
2
2
2

2
2015-07-01
3
3
3
3

3
2015-05-01
1
1
1
1

3
2015-05-02
1
1
1
1

3
2015-05-04
1
1
1
1



SELECT 14
person_id	contact_day	dash_group
1	2015-02-09	0
1	2015-05-01	1
1	2015-05-02	1
1	2015-05-03	1
1	2015-06-01	2
1	2015-08-01	3
1	2015-08-04	3
1	2015-09-01	4
2	2015-05-01	0
2	2015-06-01	1
2	2015-07-01	2
3	2015-05-01	0
3	2015-05-02	0
3	2015-05-04	0