How can I create user subscription revenue analytics with laravel query builder

I have a subscriptions table that looks like this

Subscription table

| id | user_id | service_id |amoun_paid|created_at|
| -- | ------- | ---------- |----------|----------|
| 1  | 2       | 1          |8.99      |2022-09-09|
| 2  | 200     | 3          |89.99     |2022-09-20|
| 3  | 212     | 4          |90.99     |2022-09-19|
| 4  | 209     | 5          |10.99     |2022-09-05|

Then a service table

| id | service_name | price |created_at|
| -- | ------- | ---------- |----------|
| 1  | Regular | 8.99       |2022-09-09|
| 2  | Bronze  | 89.99      |2022-09-20|
| 3  | Silver  | 90.99      |2022-09-19|
| 4  | Nickel  | 10.99      |2022-09-05|

And Then a user table

| id | name    |created_at|
| -- | ------- | ---------|
| 1  | Jumla   |2022-09-09|
| 2  | Burito  |2022-09-20|
| 3  | John    |2022-09-19|
| 4  | Mikel   |2022-09-05|

Now what I want to do with laravel query builder is to check the subscription table for how many times the user_id subscribed and then give if the user subscribed more than one time i'll then it an alias of new subscription and if its more than one time it'll be a re-subscription and then get the sum of the amount paid and i want the result to be like this

 "revenue": [
        {
            "type": "new",
            "week_1": 5000,
            "week_2": 5000,
            "week_3": 0,
            "week_4": 4000
        },
        {
            "type": "re-subscription",
            "week_1": 10000,
            "week_2": 0,
            "week_3": 0,
            "week_4": 0
        }
    ],

what I have done so far but doesn't seem to work as I expected is

To get the weeks in a month

 private function getWeeks($date)
    {
        $start = (clone $date)->startOfMonth();
        $startOfMonth = clone $start;
        $weeks_array = [];
        $count = 1;

        while (intval($start->month) == intval($date->month)) {
            $week_array[] = [
                'startOfWeek' => $start->toDateString(),
                'endOfWeek' => $start->addDays(7)->isSameMonth($startOfMonth)
                    ? $start->toDateString()
                    : $startOfMonth->endOfMonth()->toDateString(),
                'week' => $count
            ];

            $count += 1;
            $start->addDay();
        }

        return $week_array;
    }

Then get the revenue

$subscriptions = DB::table('subscription')
            ->join('services', 'services.id', 'subscription.service_id')
            ->join('patients', 'subscription.patient_id', '=', 'patients.id')
            ->groupBy('type', 'subscription.patient_id');

        foreach ($weeks as $week) {
            $subscriptions->addSelect([
                    DB::raw(
                        "(CASE WHEN count(*) = 1 THEN 'new' "
                        ."WHEN count(*) > 1 THEN 'renewal' END) AS type"
                    ),
                DB::raw(
                    "SUM("
                    ."CASE WHEN date(user_transactions.created_at) >= '{$week['startOfWeek']}' "
                    ."AND date(user_transactions.created_at) <= '{$week['endOfWeek']}' "
                    ."THEN user_transactions.service_amount_ngn ELSE 0 END) AS week_{$week['week']}"
                )
                ]);

        }

        return $subscriptions->get();

Please I need help.