sql - Take minimum dated record and count subsequent records in a specific timeframe

Question

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

Answer 1

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.