From a062af23b2e72c38b667e969652aefcf2238dc1b Mon Sep 17 00:00:00 2001
From: nsfisis <nsfisis@gmail.com>
Date: Thu, 8 Aug 2024 03:57:16 +0900
Subject: feat(backend): aggregate testcase results

---
 backend/query.sql | 14 ++++++++++++++
 1 file changed, 14 insertions(+)

(limited to 'backend/query.sql')

diff --git a/backend/query.sql b/backend/query.sql
index 0d32944..e767746 100644
--- a/backend/query.sql
+++ b/backend/query.sql
@@ -75,3 +75,17 @@ VALUES ($1, $2, $3, $4);
 -- name: CreateTestcaseResult :exec
 INSERT INTO testcase_results (submission_id, testcase_id, status, stdout, stderr)
 VALUES ($1, $2, $3, $4, $5);
+
+-- name: AggregateTestcaseResults :one
+SELECT
+    CASE
+        WHEN COUNT(CASE WHEN r.status IS NULL            THEN 1 END) > 0 THEN 'running'
+        WHEN COUNT(CASE WHEN r.status = 'internal_error' THEN 1 END) > 0 THEN 'internal_error'
+        WHEN COUNT(CASE WHEN r.status = 'timeout'        THEN 1 END) > 0 THEN 'timeout'
+        WHEN COUNT(CASE WHEN r.status = 'runtime_error'  THEN 1 END) > 0 THEN 'runtime_error'
+        WHEN COUNT(CASE WHEN r.status = 'wrong_answer'   THEN 1 END) > 0 THEN 'wrong_answer'
+        ELSE 'success'
+    END AS status
+FROM testcases
+LEFT JOIN testcase_results AS r ON testcases.testcase_id = r.testcase_id
+WHERE r.submission_id = $1;
-- 
cgit v1.3.1

