trabajar con una lista de fechas generadas en MySQL, MySQL

Language: Layout:

drop table if exists users_;

create table users_ (created_at datetime);

insert into users_ values 
('2015-01-01'),
('2016-03-05'),
('2016-03-10'),
('2017-03-31'),
('2017-04-01');

select count(u.created_at) as total_users,
       year(dateRange.rangeStart + interval (seq1000.seq) month) as created_at_year,
       month(dateRange.rangeStart + interval (seq1000.seq) month) as created_at_month
from (select date '2016-03-01' as rangeStart, date '2017-04-01' as rangeEnd) dateRange
join (
  select a.a + (10 * b.a) + (100 * c.a) as seq
  from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
  cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
  cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) seq1000
on (dateRange.rangeStart + interval (seq1000.seq) month) < dateRange.rangeEnd
left join users_ u
  on u.created_at >= dateRange.rangeStart
 and u.created_at < dateRange.rangeEnd
 and (dateRange.rangeStart + interval (seq1000.seq) month) = date(u.created_at - interval (day(u.created_at)-1) day)
group by year(dateRange.rangeStart + interval (seq1000.seq) month),
         month(dateRange.rangeStart + interval (seq1000.seq) month)
order by created_at_year, created_at_month;

drop table users_;


absolute service time: 0,34 sec