Query to find results within dynamic date ranges and group by dynamic elements

I think I am either overcomplicating this or "under-complicating" it. lol

I have a table of data that holds assessment data for students. Teachers log this assessment data on a child-by-child basis within different user-defined "terms" (date ranges) over the course of a school year. I am looking to display the progress of a selected student, grouped by each subject and displayed term-by-term.

I think what I want to do sounds a little like this when I think of it more programmatically: Grab all of the records for a given student on a term-by-term basis (term counts vary in number depending on the teacher) and average out their marks for each of the subjects that they have been assessed in (subject counts vary in number as well depending on the teacher)

This makes me think that the query might look something like this:

SELECT AVG('assessed_leve') FROM 'assessment_data' WHERE 'student_id' = $student_id AND 'created_at' >= $start_date AND 'created_at' <= $end_date GROUP BY 'subject_id'

In the above query, I would need to iterate over each teacher-defined term date range ($start_date and $end_date) and group by the subject ids.

In the Laravel world, this is what I have tried:

$user_terms = AssessmentTerm::where('user_id', 28)->get();

for($p=0; $p < count($user_terms); $p++) {

    $start_date = $user_terms[$p]->term_start;
    $end_date = $user_terms[$p]->term_end;


    // get the averages for this student where it fits the term date span, grouped by subject id
    $term_subject_average[] = DB::table('assessment_data')
        ->join('subjects', 'assessment_data.subject_id', '=', 'subjects.id')
        ->selectRaw('subjects.name as Subject, ROUND(AVG((assessment_data.assessed_level / 4) * 100)) Average')
        ->where('assessment_data.user_id', Auth::user()->id) 
        ->where('assessment_data.student_id', $student_id)
        ->whereBetween('assessment_data.created_at', [$start_date, $end_date])
        ->groupBy('assessment_data.subject_id')
        ->get();
}

(I determine how many terms the user has established for their school year, loop through each one and use the term_start and term_end dates in a whereBetween to confine the data request. Within the join statement, I am also pulling data from a 'Subjects' table which will give me subject titles instead of reference ids.)

In my sample database, I have set my test user to have three terms. Within Term 1, they have no assessment data, Term 2 has 1 result, and Term 3 has many results. However, when I run the above query I am only seeing results for Term 3 (the last iteration of the loop) when I do a dd($term_subject_average);.

array:3 [▼
  0 => Collection {#410 ▼
    #items: []
  }
  1 => Collection {#424 ▼
    #items: array:1 [▼
      0 => {#434 ▼
        +"Subject": "Social Studies"
        +"Average": 100.0
      }
    ]
  }
  2 => Collection {#433 ▼
    #items: array:7 [▼
      0 => {#439 ▼
        +"Subject": "Career and Technology Foundations"
        +"Average": 100.0
      }
      1 => {#432 ▼
        +"Subject": "English Language Arts"
        +"Average": 100.0
      }
      2 => {#440 ▼
        +"Subject": "Art"
        +"Average": 50.0
      }
      3 => {#431 ▼
        +"Subject": "Health and Life Skills"
        +"Average": 75.0
      }
      4 => {#436 ▼
        +"Subject": "Mathematics"
        +"Average": 100.0
      }
      5 => {#437 ▼
        +"Subject": "Physical Education"
        +"Average": 50.0
      }
      6 => {#438 ▼
        +"Subject": "Science"
        +"Average": 83.0
      }
    ]
  }
]

Have I gone about this the right way? If so, what do I need to do to my query in order to get the desired three-term grouped results? If not, would someone be willing to steer me in the right direction for solving this?

Thanks!