SQLize Online / PHPize Online  /  SQLtest Online

-- creating simple table create table Users ( id int not null, pseudo varchar(15), date date ); -- adding some data insert into Users values (1,'jonh','1970-01-01'), (2,'doe','1970-01-02'), (3,'janeyes','1970-01-02'), (4,'third','1970-01-03'), (5,'pseudo','1970-01-03'), (6,'title','1970-01-04'), (7,'somename','1970-01-04'), (8,'anothername','1970-01-04'); -- defines the start date and the end date set @startDate = '1970-01-01'; set @endDate = '1970-02-01'; -- recursively geneterates all dates within the range with RECURSIVE dateRange (Date) as ( select @startDate as Date union ALL select DATE_ADD(Date, INTERVAL 1 DAY) from dateRange where Date < @endDate ) -- using SUM() over () would result in running total starting from 1, it would count next day + all previous days select Date, Sum(RegisteredUsersCount) over(order by RegisteredUsersCount asc rows between unbounded preceding and current row) as RegisteredUsersCount from ( -- left join will join all users, if there is no users that correspond to the date of join, then it would be 0 for that date. select dr.Date, Count(u.id) as RegisteredUsersCount from dateRange as dr left join Users as u on dr.Date = u.date group by dr.Date ) as t order by Date asc;