Historical average per month

One possible solution using Laravel's Eloquent ORM is to group the transactions by month and then calculate the average for each month. Here's an example:

$averages = DB::table('stock_transactions')
    ->select(DB::raw('MONTH(stock_date) as month'), DB::raw('YEAR(stock_date) as year'), DB::raw('COUNT(*) as count'))
    ->groupBy('year', 'month')
    ->orderBy('year', 'asc')
    ->orderBy('month', 'asc')
    ->get()
    ->groupBy('month')
    ->map(function ($group) {
        $total = $group->sum('count');
        $count = $group->count();
        return $total / $count;
    });

// To get the historical average for June:
$historicalAverage = $averages[6];

This code uses the DB facade to build a query that selects the month and year of each transaction, counts the number of transactions for each month, and groups the results by month and year. It then orders the results by year and month, and maps the grouped results to calculate the average for each month.

To get the historical average for a specific month, you can access the corresponding value in the $averages collection using the month number as the key. For example, to get the historical average for June, you can use $averages[6].