How to group statistics data monthly using CTE (Common Table Expressions) in Laravel

cte_laravel.php

<?php

use App\Models\Order;
use Illuminate\Database\Query\JoinClause;
use Illuminate\Support\Facades\DB;
use Staudenmeir\LaravelCte\Query\Builder;

$ordersData = Order::query()
            ->selectRaw("DATE_FORMAT(created_at, '%m/%Y') AS period, COUNT(*) AS orders_count")
            ->groupByRaw("DATE_FORMAT(created_at, '%m/%Y')");

/* @var Builder $query */
$query = DB::query();

// dates is our CTE
$statisticsPerMonth = $query
    ->from('dates')
    ->withRecursiveExpression("dates", static function(Builder $query) {
        $query->selectRaw("DATE(LAST_DAY(MIN(created_at)))")
            ->from('orders')
            ->unionAll(function (Builder $query) {
                $query->selectRaw("DATE(LAST_DAY(date)) + INTERVAL 1 MONTH")
                    ->from('dates')
                    ->whereRaw("DATE(LAST_DAY(date)) <= (
                        SELECT
                            DATE(MAX(created_at))
                        FROM
                            `orders`
                    )");
            });
    }, ['date'])
    ->selectRaw("DATE_FORMAT(dates.date, '%m/%Y') AS 'period', COALESCE(orders_count, 0) AS orders_count")
    ->leftJoinSub($ordersData, 'orders', function (JoinClause $join) {
        $join->on(DB::raw("DATE_FORMAT(dates.date, '%m/%Y')"),'=','orders.period');
    })
    ->get();

// Example output:
//        0 => {#1414 ▼
//            +"period": "03/2022"
//            +"orders_count": 9
//        }
//        1 => {#1418 ▼
//            +"period": "04/2022"
//            +"orders_count": 0
//        }

// Generated SQL:
$generatedSql = <<<SQL
WITH RECURSIVE `dates` (
	`date`
) AS ((
		SELECT
			DATE(LAST_DAY(MIN(created_at)))
		FROM
			`orders`)
	UNION ALL (
		SELECT
			DATE(LAST_DAY(date)) + INTERVAL 1 MONTH
		FROM
			`dates`
		WHERE
			DATE(LAST_DAY(date)) <= (
				SELECT
					DATE(MAX(created_at))
				FROM
					`orders`))
)
SELECT
	DATE_FORMAT(dates.date, '%m/%Y') AS 'period',
	COALESCE(orders_count, 0) AS orders_count
FROM
	`dates`
	LEFT JOIN (
		SELECT
			DATE_FORMAT(created_at, '%m/%Y') AS period,
			COUNT(*) AS orders_count
		FROM
			`orders`
		GROUP BY
			DATE_FORMAT(created_at, '%m/%Y')) AS `orders` ON DATE_FORMAT(dates.date, '%m/%Y') = `orders`.`period`
  SQL

// Seeder:
use App\Models\Order;
use Carbon\Carbon;
use Illuminate\Database\Console\Seeds\WithoutModelEvents;
use Illuminate\Database\Seeder;

class OrderSeeder extends Seeder
{
    // Months between have 0 orders
    private const ORDERS_PER_PERIOD = [
        '03/2022' => 9,
        '06/2022' => 55,
        '01/2023' => 23,
        '12/2023' => 19,
    ];

    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        foreach (self::ORDERS_PER_PERIOD as $period => $count) {
            Order::factory()->count($count)->create([
                'created_at' => Carbon::createFromFormat('m/Y', $period)
            ]);
        }
    }
}