Insert Into tbl VALUES

('A', '2024-06-30 11:22:03'),

('A', '2024-06-16 14:02:36'),

('A', '2024-06-15 08:15:57'),

('A', '2024-05-24 14:30:57'),

('A', '2024-05-10 04:45:06'),

('A', '2024-05-08 15:19:48'),

('A', '2024-05-07 15:09:14'),

('A', '2024-05-06 04:16:39'),

('A', '2024-05-04 10:37:16'),

('A', '2024-04-08 05:02:00'),

('A', '2024-04-06 09:03:23'),

('A', '2024-03-30 11:05:55'),

('A', '2024-03-16 08:39:56'),

('A', '2024-03-15 14:06:10'),

('A', '2024-02-28 16:55:28');

dates as

( Select Row_Number() Over(Partition By d.identifier, d.dt_0 Order By d.dt_1) as rn_0,

d.identifier, d.dt_0, d.dt_1, d.diff

From ( Select t1.identifier, Date_Trunc('day', t1.dt) as dt_1,

Case When DATE_PART('day', Date_Trunc('day', t1.dt) - Date_Trunc('day', t2.dt)) >= 30

Then Date_Trunc('day', t2.dt)

End as dt_0,

DATE_PART('day', Date_Trunc('day', t1.dt) - Date_Trunc('day', t2.dt)) as diff,

Min(Date_Trunc('day', t1.dt)) Over(Partition By t1.identifier) as start_dt

From tbl t1

Inner Join tbl t2 ON(t1.identifier = t2.identifier)

) d

Where diff >= 30

),

grid as

( Select Distinct d0.*, d1.dt_0 as dt_00, d1.dt_1 as dt_11

From dates d0

Inner Join dates d1 on(d1.identifier = d0.identifier And

d1.dt_0 = d0.dt_1 And

d0.rn_0 = 1 And d1.rn_0 = 1

)

Where d0.rn_0 = 1 And d0.dt_1 = d1.dt_0

),

reff_dates as

( Select identifier, Min(dt_0) as dt_0 From grid Group By identifier UNION

Select identifier, Min(dt_1) From grid Group By identifier UNION

Select identifier, Min(dt_11) From grid Group By identifier UNION

Select Distinct identifier, dt_11 From grid

)

Select t.identifier, t.dt,

Case When r.dt_0 Is Null Then 'FALSE'

Else 'TRUE'

End as new_column

From tbl t

Left Join reff_dates r ON( r.dt_0 = Date_Trunc('day', t.dt) )