Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit


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)
;
alter table mydata add column dateym text;
update mydata set dateym = to_char(contact_day,'YYYY-MM');
14 rows affected

14 rows affected

select person_id,contact_day,dateym,
    rank() over (partition by (person_id, dateym) order by person_id, contact_day ),
    dense_rank() over ( partition by person_id order by  person_id, dateym )
    from mydata;


person_id
contact_day
dateym
rank
dense_rank


1
2015-02-09
2015-02
1
1

1
2015-05-01
2015-05
1
2

1
2015-05-02
2015-05
2
2

1
2015-05-03
2015-05
3
2

1
2015-06-01
2015-06
1
3

1
2015-08-01
2015-08
1
4

1
2015-08-04
2015-08
2
4

1
2015-09-01
2015-09
1
5

2
2015-05-01
2015-05
1
1

2
2015-06-01
2015-06
1
2

2
2015-07-01
2015-07
1
3

3
2015-05-01
2015-05
1
1

3
2015-05-02
2015-05
2
1

3
2015-05-04
2015-05
3
1

person_id	contact_day	dateym	rank	dense_rank
1	2015-02-09	2015-02	1	1
1	2015-05-01	2015-05	1	2
1	2015-05-02	2015-05	2	2
1	2015-05-03	2015-05	3	2
1	2015-06-01	2015-06	1	3
1	2015-08-01	2015-08	1	4
1	2015-08-04	2015-08	2	4
1	2015-09-01	2015-09	1	5
2	2015-05-01	2015-05	1	1
2	2015-06-01	2015-06	1	2
2	2015-07-01	2015-07	1	3
3	2015-05-01	2015-05	1	1
3	2015-05-02	2015-05	2	1
3	2015-05-04	2015-05	3	1