CREATE TYPE date_and_rn AS (

    d  date,

    rn int

);

CREATE OR REPLACE FUNCTION reduce(previous_state date_and_rn, current_row date, max_interval interval) RETURNS date_and_rn AS

$$

    SELECT CASE

        -- still within the max range, we simply increment rn

        WHEN ((current_row - (previous_state).d) || ' days')::interval <= max_interval THEN ((previous_state).d, (previous_state).rn + 1)::date_and_rn

        -- new group, starting from the current row

        ELSE (current_row, 1)::date_and_rn

    END;

$$

IMMUTABLE

LANGUAGE SQL;

CREATE OR REPLACE FUNCTION finalizer(output date_and_rn) RETURNS int AS

$$

    SELECT (output).rn;

$$

IMMUTABLE

STRICT

LANGUAGE SQL;

CREATE AGGREGATE not_a_good_name(date, interval)

(

    SFUNC = reduce,

    STYPE = date_and_rn,

    FINALFUNC = finalizer

);

WITH values(x) AS (

    VALUES (1), (2), (3), (5), (6)

), as_dates(d) AS (

    SELECT ('2020-01-01'::date + (x || ' months')::interval)::date

    FROM values

)

SELECT d, not_a_good_name(d, '3 months'::interval) OVER (ORDER BY d)

FROM as_dates

;

/* OUTPUT

2020-02-01;1

2020-03-01;2

2020-04-01;3

2020-06-01;1

2020-07-01;2

*/