I have the following example table:
User | Date |
---|---|
A | 2016-01-01 |
A | 2017-01-01 |
A | 2018-01-01 |
A | 2019-01-01 |
B | 2017-03-05 |
B | 2017-11-01 |
Use a window function to get the minimum date. Then aggregate:
select user,
count(*) filter (where date > min_date and date < min_date + interval '8 month')
from (select t.*, min(date) over (partition by user) as min_date
from t
) t
group by user;
Here is a db<>fiddle.
Note: user
is a reserved word so it should not be used as a column name.