clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1926523 fiddles created (21785 in the last week).

create table users (id int, name text, createdAt timestamp); insert into users values (1, 'Timothee', '2018-03-01 13:02:20.904+00'); create table friends (id int, userId int, friendId int, createdAt timestamp); insert into friends values (1, 1, 234, '2018-03-20 15:41:51.779+00'), (2, 1, 254, '2018-03-20 16:16:34.698+00'), (3, 1, 288, '2018-03-15 15:17:39.907+00'), (4, 1, 293, '2018-03-07 16:15:49.379+00');
1 rows affected
4 rows affected
 hidden batch(es)


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
ddate ddays
2018-03-01 13:02:20.904 87 days
2018-05-27 13:02:20.904 87 days
2018-08-22 13:02:20.904 87 days
2018-11-17 13:02:20.904 87 days
2019-02-12 13:02:20.904 87 days
2019-05-10 13:02:20.904 87 days
2019-08-05 13:02:20.904 87 days
2019-10-31 13:02:20.904 87 days
2020-01-26 13:02:20.904 87 days
2020-04-22 13:02:20.904 87 days
 hidden batch(es)


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 >= ddate and createdAt < ddate + ddays) friends from x;
ddate friends
2018-03-01 13:02:20.904 4
2018-05-27 13:02:20.904 0
2018-08-22 13:02:20.904 0
2018-11-17 13:02:20.904 0
2019-02-12 13:02:20.904 0
2019-05-10 13:02:20.904 0
2019-08-05 13:02:20.904 0
2019-10-31 13:02:20.904 0
2020-01-26 13:02:20.904 0
2020-04-22 13:02:20.904 0
 hidden batch(es)


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 >= ddate and createdAt < ddate + ddays group by ddate order by ddate;
ddate friends
2018-03-01 13:02:20.904 4
2018-05-27 13:02:20.904 0
2018-08-22 13:02:20.904 0
2018-11-17 13:02:20.904 0
2019-02-12 13:02:20.904 0
2019-05-10 13:02:20.904 0
2019-08-05 13:02:20.904 0
2019-10-31 13:02:20.904 0
2020-01-26 13:02:20.904 0
2020-04-22 13:02:20.904 0
 hidden batch(es)


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 >= ddate and createdAt < ddate + ddays group by ddate order by ddate;
ddate friends friends_total
2018-03-01 13:02:20.904 4 4
2018-05-27 13:02:20.904 0 4
2018-08-22 13:02:20.904 0 4
2018-11-17 13:02:20.904 0 4
2019-02-12 13:02:20.904 0 4
2019-05-10 13:02:20.904 0 4
2019-08-05 13:02:20.904 0 4
2019-10-31 13:02:20.904 0 4
2020-01-26 13:02:20.904 0 4
2020-04-22 13:02:20.904 0 4
 hidden batch(es)