SELECT
  id, 
 tutor_id, 
  AVG(overall) AS Overall,
  AVG(engagement) AS Engagement, 
  AVG(consistency) AS Consistency, 
  AVG(justification) AS Justification, 
  AVG(argument) AS Argument
FROM (
  SELECT
    t.participants.purple AS id,
    t.participants.red AS tutor_id,
    r.result.scores.overall AS overall,
    r.result.scores.consistency_of_belief AS consistency,
    r.result.scores.justification_quality AS justification,
    r.result.scores.argument_adaptation AS argument,
    r.result.scores.engagement AS engagement
  FROM results t
  CROSS JOIN UNNEST(t.results) AS r(result)
)
GROUP BY id, 
 tutor_id 
ORDER BY overall DESC, engagement DESC, id;