SELECT id,
       ROUND(100.0 * success / NULLIF(n, 0), 1) AS "SUCCESS%",
       ROUND(avg_ps, 2) AS "Primitive",
       ROUND(avg_ns, 2) AS "Negotiation",
       ROUND(avg_ic, 2) AS "Impl",
       ROUND(avg_tu, 2) AS "Tool",
       ROUND(avg_ss, 2) AS "Security",
       ROUND(avg_time, 2) AS "Time(s)"
FROM (
  SELECT
    results.participants.agent AS id,
    COUNT(*) AS n,
    SUM(CASE WHEN res.summary.outcomes.SUCCESS > 0 THEN 1 ELSE 0 END) AS success,
    AVG(res.summary.mean_scores."Primitive Selection") AS avg_ps,
    AVG(res.summary.mean_scores."Negotiation Skills") AS avg_ns,
    AVG(res.summary.mean_scores."Implementation Correctness") AS avg_ic,
    AVG(res.summary.mean_scores."Computation / Tool Usage") AS avg_tu,
    AVG(res.summary.mean_scores."Security Strength") AS avg_ss,
    AVG(res.elapsed_s) AS avg_time
  FROM results
  CROSS JOIN UNNEST(results.results) AS r(res)
  GROUP BY id
)
ORDER BY "SUCCESS%" DESC, "Time(s)" ASC;