44021237, MySQL - rextester

Language: Layout:

drop table if exists userss;

create table userss(
    created_at date,
    id int
);

insert into userss values
('2016-01-01', 1),
('2016-02-01', 1),
('2016-04-01', 1),
('2016-04-01', 1),
('2016-06-01', 1),
('2016-09-01', 1),
('2017-02-01', 1),
('2017-04-01', 1),
('2017-05-01', 1),
('2017-05-01', 1),
('2017-10-01', 1),
('2017-12-01', 1);

select  t1.ym, sum(t2.cnt)
from    (
            select  concat(year(created_at), lpad(month(created_at), 2, '0')) as ym,
                    count(*) as cnt
            from    userss
            group by concat(year(created_at), lpad(month(created_at), 2, '0'))
        ) t1
join    (
            select  concat(year(created_at), lpad(month(created_at), 2, '0')) as ym,
                    count(*) as cnt
            from    userss
            group by concat(year(created_at), lpad(month(created_at), 2, '0'))
        ) t2
on      t1.ym >= t2.ym
group by t1.ym
order by 1;

select  t1.mnt, sum(t2.cnt)
from    (
            select  month(created_at) as mnt,
                    count(*) as cnt
            from    userss
            group by month(created_at)
        ) t1
join    (
            select  month(created_at) as mnt,
                    count(*) as cnt
            from    userss
            group by month(created_at)
        ) t2
on      t1.mnt >= t2.mnt
group by t1.mnt
order by t1.mnt;

select  t1.yr, t1.mn, sum(t2.cnt)
from    (
            select  year(created_at) as yr, month(created_at) as mn,
                    count(*) as cnt
            from    userss
            group by year(created_at), month(created_at)
        ) t1
join    (
            select  year(created_at) as yr, month(created_at) as mn,
                    count(*) as cnt
            from    userss
            group by year(created_at), month(created_at)
        ) t2
on      t1.yr = t2.yr and
        t1.mn >= t2.mn
group by t1.yr, t1.mn
order by t1.yr, t1.mn;


absolute service time: 0,67 sec