SELECT id, ROUND(AVG(success_rate), 1) AS "Success Rate %", ROUND(AVG(avg_score), 2) AS "Mean Score (0-1)", ROUND(AVG(avg_actions), 1) AS "Avg Turns per Task", SUM(tasks) AS "Total Tasks Evaluated" FROM (SELECT r.participants.shopper AS id, (assessment.aggregate.successful_tasks * 100.0 / assessment.aggregate.total_tasks) AS success_rate, assessment.aggregate.average_score AS avg_score, assessment.aggregate.total_tasks AS tasks, (SELECT AVG(task.actions_taken) FROM UNNEST(assessment.results) AS t(task)) AS avg_actions FROM results r CROSS JOIN UNNEST(r.results) AS a(assessment)) GROUP BY id ORDER BY "Success Rate %" DESC, "Mean Score (0-1)" DESC, "Avg Turns per Task" ASC;

SELECT id, ROUND(AVG(budget_sr) * 100, 0) AS "Budget Mgmt", ROUND(AVG(memory_sr) * 100, 0) AS "Preference Memory", ROUND(AVG(recovery_sr) * 100, 0) AS "Error Recovery", ROUND(AVG(constraint_sr) * 100, 0) AS "Constraint Satisfaction", ROUND(AVG(reasoning_sr) * 100, 0) AS "Comparative Reasoning" FROM (SELECT r.participants.shopper AS id, CAST(assessment.aggregate.by_task_type.budget_constrained.success_rate AS DOUBLE) AS budget_sr, CAST(assessment.aggregate.by_task_type.preference_memory.success_rate AS DOUBLE) AS memory_sr, CAST(assessment.aggregate.by_task_type.error_recovery.success_rate AS DOUBLE) AS recovery_sr, CAST(assessment.aggregate.by_task_type.negative_constraint.success_rate AS DOUBLE) AS constraint_sr, CAST(assessment.aggregate.by_task_type.comparative_reasoning.success_rate AS DOUBLE) AS reasoning_sr FROM results r CROSS JOIN UNNEST(r.results) AS a(assessment)) GROUP BY id ORDER BY id;

SELECT id, ROUND(AVG(budget_acts), 1) AS "Budget Turns", ROUND(AVG(memory_acts), 1) AS "Memory Turns", ROUND(AVG(recovery_acts), 1) AS "Recovery Turns", ROUND(AVG(constraint_acts), 1) AS "Constraint Turns", ROUND(AVG(reasoning_acts), 1) AS "Reasoning Turns" FROM (SELECT r.participants.shopper AS id, (SELECT AVG(task.actions_taken) FROM UNNEST(assessment.results) AS t(task) WHERE task.task_type = 'budget_constrained') AS budget_acts, (SELECT AVG(task.actions_taken) FROM UNNEST(assessment.results) AS t(task) WHERE task.task_type = 'preference_memory') AS memory_acts, (SELECT AVG(task.actions_taken) FROM UNNEST(assessment.results) AS t(task) WHERE task.task_type = 'error_recovery') AS recovery_acts, (SELECT AVG(task.actions_taken) FROM UNNEST(assessment.results) AS t(task) WHERE task.task_type = 'negative_constraint') AS constraint_acts, (SELECT AVG(task.actions_taken) FROM UNNEST(assessment.results) AS t(task) WHERE task.task_type = 'comparative_reasoning') AS reasoning_acts FROM results r CROSS JOIN UNNEST(r.results) AS a(assessment)) GROUP BY id ORDER BY id;