Assessments

-- Get a list of student assessments and their responses
SELECT
  sa.assessment_id,
  sa.version_id,
  v."number" AS version_number,
  sa.student_assessment_id,
  sa.student_id,
  sa.date_taken,
  (
    SELECT json_agg(r.response ORDER BY f.sort_order[v.number])
    FROM dna_assessments.fields f
    LEFT JOIN dna_assessments.students_assessments_responses sar
      ON sar.field_id = f.field_id
        AND sar.student_assessment_id = sa.student_assessment_id
    LEFT JOIN dna_assessments.responses r ON r.response_id = sar.response_id
    WHERE f.assessment_id = sa.assessment_id
  ) AS responses
FROM dna_assessments.students_assessments sa
JOIN dna_assessments.versions v USING (version_id)
LIMIT 10

-- Get a list of student scores for an assessment
SELECT
  students.first_name,
  students.last_name,
  agg.percent_correct,
  pbands.label AS pband,
  pbands.color AS pband_color
FROM dna_assessments.agg_student_responses AS agg
JOIN dna_assessments.performance_bands AS pbands USING (performance_band_id)
JOIN students USING (student_id)
WHERE assessment_id = ?
LIMIT 100

-- Get a list of EL student scores for an assessment
SELECT
  students.local_student_id,
  standard_person_name(
    students.last_name,
    students.first_name,
    students.middle_name
  ) AS student_name,
  agg.points,
  agg.points_possible,
  agg.percent_correct,
  pbands.label AS pband,
  pbands.color AS pband_color
FROM dna_assessments.agg_student_responses AS agg
JOIN dna_assessments.performance_bands AS pbands USING (performance_band_id)
JOIN students USING (student_id)
WHERE assessment_id IN (?) -- replace the ? with your assessment_ids
  AND students.english_proficiency IN (
    -- Get english learner code
    SELECT code_id
    FROM codes.english_proficiency
    WHERE system_key = 'english_learner'
  )
ORDER BY students.last_name,
  students.first_name
LIMIT 100