Can this process be done with a single SQL query?

Question

MySQL

There is a table with columns: id, channel_id, participants_count, created_at.
And there is a model ChannelDataHistoryfor working with this table.
I need to get the sum of the participants_countmaximum value of the channel ( channel_id) by days for a certain period of time.
Now I do this:

// ...

// Определяю промежуток времени
$datetime_begin = Carbon::today()->subDays(13)->toDateTimeString();
$datetime_end = Carbon::today()->subDay()->toDateTimeString();

// Получаю все данные за определенное промежуток времени
// и группирую их хелпером коллекции `groupBy()` по дням
$data_history_by_days = ChannelDataHistory::whereBetween('created_at', [$datetime_begin, $datetime_end])
    ->orderBy('created_at', 'ASC')
    ->get()
    ->groupBy(function ($query) {
        return $query->created_at->format('d');
    });

// Получаю метки для графика в виде дней (..., 28, 29, 30, 1, 2, 3, 4, ...)
$chart_labels = $data_history_by_days->keys();

// Получаю сумму максимальных значений каналов по дням
$chart_dataset = $data_history_by_days->map(function ($grouped_by_days) {
    return $grouped_by_days->groupBy('channel_id')
        ->map(function ($grouped_by_channel_id) {
            return $grouped_by_channel_id->max('participants_count');
        });
    })
    ->map(function ($item) {
        return $item->sum();
    })
    ->values();
// ...

Everything would be fine if all this worked
smartly :) All this, of course, is slowing down the processing of the received data using SQL. Is it possible to do all this with one (or maybe two) only SQL query, or at least somehow significantly optimize.
PS Of course, I use caching for 30 minutes, but after every 1st, 2nd request, the page crashes after 30 minutes.

Answer 1

Why don't you group within the query itself? I think it should be something like this

ChannelDataHistory::whereBetween('created_at', $datetimes)
    ->groupBy('date')
    ->get([
        DB::raw('Date(created_at) as date'),
        DB::raw('MAX(participants_count) as maxCount'),
        .....
     ]);