How to do a cumulative count using Raw SQl / Laravel - Eloquent ORM

Question

This is more of a SQL question than a Laravel one.

I'd like to accomplish something like the following based on my User model with the created_at field. (table users in database)

created_at	user_id
2022-04-30	1
2022-05-02	2
2022-05-03	4

date	created_users_to_this_date	total_users_created_to_date
2022-04	1	1
2022-05	2	3

Any idea on how to do so ?

What I have done so far (using Eloquent ORM) :

User::query()
   ->selectRaw("COUNT(*) created_users_to_this_date, DATE_FORMAT(created_at, '%Y-%m') date")
   ->orderBy('date')
   ->groupBy('date')
   ->get();

Equivalent SQL request

select COUNT(*) created_users_to_this_date, DATE_FORMAT(created_at, '%Y-%m') date from `users` where `users`.`deleted_at` is null group by `date` order by `date` asc

Thus returning

date	created_users_to_this_date
2022-04	1
2022-05	2

I thank you for your help

Answer 1

If your mysql version support window function, you can try to use SUM window function to do cumulative count

DB::table(DB::raw('(select COUNT(*) created_users_to_this_date, DATE_FORMAT(created_at, \'%Y-%m\') date 
    from `users` 
    where `users`.`deleted_at` is null 
    group by `date`) t1'))
->select('created_users_to_this_date','date',DB::raw('SUM(created_users_to_this_date) OVER(ORDER BY date) total_users_created_to_date'))
->get();

Answer 2

your equivalent sql will be

SELECT DATE ,
  @running_number:=@running_number+created_users_to_this_date AS created_users_to_this_date
   FROM (SELECT 
  COUNT(*) AS created_users_to_this_date,
  DATE_FORMAT(created_at, '%Y-%m') DATE
FROM
  users 
where users.deleted_at is null 
GROUP BY `date` 
ORDER BY `date` ASC ) final
JOIN (SELECT @running_number:=0) rn

Answer 3

User::query()
 ->select('
 DB::raw('COUNT(DATE_FORMAT(created_at, \'%Y-%m\') = DATE_FORMAT(now(), \'%Y-%m\')) as created_users_to_this_date'),
 DB::raw('COUNT(DATE_FORMAT(created_at, \'%Y-%m\') <= DATE_FORMAT(now(), \'%Y-%m\')) as total_users_created_to_date'),
 DB::raw('DATE_FORMAT(created_at, '%Y-%m') as date')
 ')->orderBy('date')->groupBy('date')->get();