How to make query grouping by month and nested calculation of results by condition?

Question

Laravel

Greetings,
please tell me how to implement a grouping of the number of rows up to a certain date and then count the rows inside the group by condition?
For example:
New users are added every month and I need to display statistics about them as the number of users increased. I need to subtract exactly a month from the current date, today is July 12, then June 12, May 12, etc. and find out how many users there were at that time, then users have groups: user, customer and the "everyone" group.
Now the query recursively gets users from the database for each month, then I myself create an array of data from this. If this can be implemented through Eloquent, I will be grateful for an example.

select count(*) from `users` where created_at <= `2019-07-12`
select count(*) from `users` where created_at <= `2019-07-12` and type = 'user'
select count(*) from `users` where created_at <= `2019-07-12` and type = 'customer'

select count(*) from `users` where created_at <= `2019-06-12`
select count(*) from `users` where created_at <= `2019-06-12` and type = 'user'
select count(*) from `users` where created_at <= `2019-06-12` and type = 'customer'

$stats[] = [
            '2019-07-12' => [
              'all' => $this->countAll(),
              'users' => $this->countUsers(),
              'customers' => $this->countCustomers(),
            ]
        ];

Answer 1

As an option not to make 3 requests

select count(*), sum(type = 'user'), sum(type = 'customer') from `users` where created_at <= `2019-07-12`

Answer 2

Maybe something like this.

select
    sum(case when type = "all" then 1 else 0 end) as "all",
from (values ('2019-01-12', '2019-07-12')) AS dates (dateStart, dateEnd)
 inner join users on users.created_at > dates.dateStart::date and users.created_at < dates.dateEnd::date

group by dates.dateStart::date

But it is correct to do such a grouping already in php. And in sql

select dates.dateStart::date, type, count(*)
group by dates.dateStart::date

Answer 3

I use Carbon to work with dates. They drive into a loop (for example, addMonth or subMonth) and a condition, and at the output I get an array of data with ready-made results. Maybe my answer will help you a little, because I don't like "raw" requests