Postgres 12

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

CREATE TABLE test (id INT, date1 DATE, date2 DATE);
INSERT INTO test VALUES
(11,        '01.01.2000',     '01.01.2000'),
(22,        '01.01.2001',     '01.01.2003'),
(22,        '01.01.2002',     '01.01.2003'),
(33,        '01.01.2005',     '01.01.2000'),
(33,        '01.01.2005',     '01.01.2000'),
(33,        '01.01.2007',     '01.01.2006');
SELECT * FROM test;
6 rows affected



id
date1
date2


11
2000-01-01
2000-01-01

22
2001-01-01
2003-01-01

22
2002-01-01
2003-01-01

33
2005-01-01
2000-01-01

33
2005-01-01
2000-01-01

33
2007-01-01
2006-01-01


WITH cte AS ( SELECT id, MAX(date2) date2
              FROM test
              GROUP BY id )
SELECT cte.id, COUNT(test.date1) cnt
FROM cte
LEFT JOIN test ON test.id = cte.id
              AND test.date1 < cte.date2
GROUP BY cte.id
ORDER BY id;


id
cnt


11
0

22
2

33
2


WITH cte AS ( SELECT id, date1, MAX(date2) OVER (PARTITION BY id) date2
              FROM test )
SELECT id, SUM((date1 < date2)::INT) cnt
FROM cte
GROUP BY id
ORDER BY id;


id
cnt


11
0

22
2

33
2

id	date1	date2
11	2000-01-01	2000-01-01
22	2001-01-01	2003-01-01
22	2002-01-01	2003-01-01
33	2005-01-01	2000-01-01
33	2005-01-01	2000-01-01
33	2007-01-01	2006-01-01