240715_TIL

아무리 사소하더라도 배움이 없는 날은 없다.

SQL 코드카타
Advanced SQL50 문제부터 풀기.

문제 링크

SELECT customer_id
FROM   customers
WHERE  year = '2021'
       AND revenue > 0; 

문제 링크

WITH a
     AS (SELECT customer_id
         FROM   orders
         WHERE  product_name = 'A'),
     b
     AS (SELECT o.customer_id
         FROM   orders o
                INNER JOIN a
                        ON o.customer_id = a.customer_id
         WHERE  o.product_name = 'B'),
     notc
     AS (SELECT customer_id
         FROM   b
         WHERE  customer_id NOT IN (SELECT customer_id
                                    FROM   orders
                                    WHERE  product_name = 'C'))
SELECT DISTINCT c.customer_id,
                c.customer_name
FROM   customers c
       INNER JOIN notc
               ON c.customer_id = notc.customer_id;

문제 링크

WITH result
     AS (SELECT DISTINCT seller_id
         FROM   orders
         WHERE  Year(sale_date) = '2020')
SELECT s.seller_name
FROM   seller s
       LEFT JOIN result r
              ON s.seller_id = r.seller_id
WHERE  r.seller_id IS NULL
ORDER  BY 1;

문제 링크

SELECT e.left_operand,
       e.operator,
       e.right_operand,
       CASE
         WHEN e.operator = '<'
              AND v1.value < v2.value THEN 'true'
         WHEN e.operator = '='
              AND v1.value = v2.value THEN 'true'
         WHEN e.operator = '>'
              AND v1.value > v2.value THEN 'true'
         ELSE 'false'
       end AS "value"
FROM   expressions e
       INNER JOIN variables v1
               ON e.left_operand = v1.name
       INNER JOIN variables v2
               ON e.right_operand = v2.name;

문제 링크

1. exam_id별로 최고 점수(maxscore)와 최저 점수(minscore)를 구하고 exam_result CTE에 저장한다.
2. exam_result 테이블과 exam 테이블을 left join하되, exam_id끼리 일치 + minscore와 일치하는 조건을 주어 최저점을 획득한 학생의 id를 구한다. 이 결과를 lowest에 저장한다.
3. 2번과 같은 방식으로 최고점을 획득한 학생의 id를 구하고 그 결과를 highest에 저장한다.
4. lowest와 highest를 합쳐서 최고점 혹은 최저점을 얻은 학생들의 id 리스트를 얻는다. 이 결과를 final_list에 저장한다.
5. 그리고 시험에 응시한 모든 학생의 고유 id를 구해서 student_list에 저장한다. (이 CTE를 만드는 이유는 한 번도 시험에 응시하지 않은 학생은 결과 출력에서 배제해야 하기 때문)
6. 본 쿼리에서는 student 테이블에서 id와 이름을 불러온다. 이 때 final_list와 student_list 2개 테이블을 student와 left join해 주고, where절에서 final_list의 student_id는 null, 즉 최고점과 최저점을 획득한 학생은 아니나 (문제에서 요구한 silent student) sl.student_id는 null이 아닌, 즉 그렇다고 시험에 응시하지 않았던 것은 아닌 경우로 출력 범위를 제한한다.

아래는 완성된 정답 쿼리.

WITH exam_result
     AS (SELECT exam_id,
                Min(score) AS "minscore",
                Max(score) AS "maxscore"
         FROM   exam
         GROUP  BY 1),
     lowest
     AS (SELECT DISTINCT student_id
         FROM   exam_result er
                LEFT JOIN exam e
                       ON er.exam_id = e.exam_id
                          AND er.minscore = e.score),
     highest
     AS (SELECT DISTINCT student_id
         FROM   exam_result er
                LEFT JOIN exam e
                       ON er.exam_id = e.exam_id
                          AND er.maxscore = e.score),
     final_list
     AS (SELECT *
         FROM   lowest
         UNION
         SELECT *
         FROM   highest),
     student_list
     AS (SELECT DISTINCT student_id
         FROM   exam)
SELECT s.student_id,
       s.student_name
FROM   student s
       LEFT JOIN final_list fl
              ON s.student_id = fl.student_id
       LEFT JOIN student_list sl
              ON s.student_id = sl.student_id
WHERE  fl.student_id IS NULL
       AND sl.student_id IS NOT NULL;