<commit_before>class ProgressReports::ActivitiesScoresByClassroom
  def self.results(classroom_ids)
    ids = classroom_ids.join(', ')
    ActiveRecord::Base.connection.execute(query(ids)).to_a
  end

  private

  def self.query(classroom_ids)
    <<~SQL
      SELECT classrooms.name AS classroom_name,
        students.id AS student_id,students.last_active,
        students.name,
        AVG(activity_sessions.percentage)
        FILTER(WHERE activities.activity_classification_id <> 6 AND activities.activity_classification_id <> 4) AS average_score,
        COUNT(activity_sessions.id) AS activity_count,
        classrooms.id AS classroom_id
      FROM classroom_units
      JOIN activity_sessions ON classroom_units.id = activity_sessions.classroom_unit_id
      JOIN activities ON activity_sessions.activity_id = activities.id
      JOIN classrooms ON classrooms.id = classroom_units.classroom_id
      JOIN users AS students ON students.id = activity_sessions.user_id
      WHERE classroom_units.classroom_id IN (#{classroom_ids})
      AND activity_sessions.is_final_score = TRUE
      AND classroom_units.visible = true
      GROUP BY classrooms.name, students.id, students.name, classrooms.id, last_active
    SQL
  end
end<commit_msg>update sql query<commit_after>class ProgressReports::ActivitiesScoresByClassroom
  def self.results(classroom_ids)
    ids = classroom_ids.join(', ')
    ActiveRecord::Base.connection.execute(query(ids)).to_a
  end

  private

  def self.query(classroom_ids)
    <<~SQL
      SELECT classrooms.name AS classroom_name,
        students.id AS student_id,students.last_active,
        students.name,
        AVG(activity_sessions.percentage)
        FILTER(WHERE activities.activity_classification_id <> 6 AND activities.activity_classification_id <> 4) AS average_score,
        COUNT(activity_sessions.id) AS activity_count,
        classrooms.id AS classroom_id
      FROM classroom_units
      JOIN activity_sessions ON classroom_units.id = activity_sessions.classroom_unit_id
      JOIN activities ON activity_sessions.activity_id = activities.id
      JOIN classrooms ON classrooms.id = classroom_units.classroom_id
      JOIN users AS students ON students.id = activity_sessions.user_id
      WHERE classroom_units.classroom_id IN (#{classroom_ids})
      AND activity_sessions.is_final_score = TRUE
      AND classroom_units.visible = true
      GROUP BY classrooms.name, students.id, students.name, classrooms.id, students.last_active
    SQL
  end
end
{'fraction_non_alphanumeric': 0.06829073482428115, 'fraction_numerical': 0.001597444089456869, 'mean_word_length': 3.892578125, 'pattern_counts': {'":': 0, '<': 11, '<?xml version=': 0, '>': 7, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2896291', 'n_tokens_mistral': 749, 'n_tokens_neox': 710, 'n_words': 159}