PostgreSQL select count with dynamic date range
Postgres allows to generate a series of dates using generate_series() function.

select generate_series(createdAt, current_date, 
       (date_part('day', current_date::timestamp - createdAt::timestamp) / 10)::int * '1 day'::interval) ddate
       , (date_part('day', current_date::timestamp - createdAt::timestamp) / 10)::int * '1 day'::interval as ddays                
from   users
where  id = 1;


It returns:



ddate                   | ddays 
:---------------------- | :-----
2018-03-01 13:02:20.904 | 2 days
2018-03-03 13:02:20.904 | 2 days
2018-03-05 13:02:20.904 | 2 days
2018-03-07 13:02:20.904 | 2 days
2018-03-09 13:02:20.904 | 2 days
2018-03-11 13:02:20.904 | 2 days
2018-03-13 13:02:20.904 | 2 days
2018-03-15 13:02:20.904 | 2 days
2018-03-17 13:02:20.904 | 2 days
2018-03-19 13:02:20.904 | 2 days



Then you can use previous query to count friends between this intervals:

with x as
(
    select generate_series(createdAt, current_date, 
           (date_part('day', current_date::timestamp - createdAt::timestamp) / 10)::int * '1 day'::interval) ddate
           , (date_part('day', current_date::timestamp - createdAt::timestamp) / 10)::int * '1 day'::interval as ddays
    from   users
    where  id = 1
)
select ddate,
       (select count(*)
        from   friends
        where  createdAt &gt;= ddate
        and    createdAt &lt;  ddate + ddays) friends
from x;


Final result:



ddate                   | friends
:---------------------- | ------:
2018-03-01 13:02:20.904 |       0
2018-03-03 13:02:20.904 |       0
2018-03-05 13:02:20.904 |       0
2018-03-07 13:02:20.904 |       1
2018-03-09 13:02:20.904 |       0
2018-03-11 13:02:20.904 |       0
2018-03-13 13:02:20.904 |       0
2018-03-15 13:02:20.904 |       1
2018-03-17 13:02:20.904 |       0
2018-03-19 13:02:20.904 |       2



Or if you prefer a join instead of a correlated scalar subquery:

with x as
(
    select generate_series(createdAt, current_date, 
           (date_part('day', current_date::timestamp - createdAt::timestamp) / 10)::int * '1 day'::interval) ddate
           , (date_part('day', current_date::timestamp - createdAt::timestamp) / 10)::int * '1 day'::interval as ddays
    from   users
    where  id = 1
)
select ddate,
       count(friends.*) as friends
from   x
left join   friends
on     createdAt &gt;= ddate
and    createdAt &lt;  ddate + ddays
group by ddate
order by ddate;


This last option can be further modified to returned running totals, as per your expected output:

with x as
(
    select generate_series(createdAt, current_date, 
           (date_part('day', current_date::timestamp - createdAt::timestamp) / 10)::int * '1 day'::interval) ddate
           , (date_part('day', current_date::timestamp - createdAt::timestamp) / 10)::int * '1 day'::interval as ddays
    from   users
    where  id = 1
)
select ddate,
       count(friends.*) as friends,
       sum(count(friends.*)) over (order by ddate) as friends_total
from   x
left join   friends
on     createdAt &gt;= ddate
and    createdAt &lt;  ddate + ddays
group by ddate
order by ddate;


Result:



ddate                   | friends | friends_total
:---------------------- | ------: | ------------:
2018-03-01 13:02:20.904 |       0 |             0
2018-03-03 13:02:20.904 |       0 |             0
2018-03-05 13:02:20.904 |       0 |             0
2018-03-07 13:02:20.904 |       1 |             1
2018-03-09 13:02:20.904 |       0 |             1
2018-03-11 13:02:20.904 |       0 |             1
2018-03-13 13:02:20.904 |       0 |             1
2018-03-15 13:02:20.904 |       1 |             2
2018-03-17 13:02:20.904 |       0 |             2
2018-03-19 13:02:20.904 |       2 |             4



dbfiddle here