241008_TIL

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

SQL 문제풀이 복습

문제 링크
1트(7/15)에서 CTE로 만들었던 부분을
오늘은 모두 서브쿼리로 처리해서 연결해 주었다.

lowest와 highest에 해당하는 student_id를 뽑고
시험에 한 번이라도 응시한 student_id를 뽑은 다음
Student 테이블과 join해서 null 조건을 주면 끝.

SELECT s.student_id,
       s.student_name
FROM   Student s
       LEFT JOIN (SELECT DISTINCT student_id
                  FROM   (SELECT exam_id,
                                 Min(score) AS "lowest",
                                 Max(score) AS "highest"
                          FROM   Exam
                          GROUP  BY 1) a
                         LEFT JOIN Exam e
                                ON a.exam_id = e.exam_id
                                   AND a.lowest = e.score
                  UNION
                  SELECT DISTINCT student_id
                  FROM   (SELECT exam_id,
                                 Min(score) AS "lowest",
                                 Max(score) AS "highest"
                          FROM   Exam
                          GROUP  BY 1) a
                         LEFT JOIN Exam e
                                ON a.exam_id = e.exam_id
                                   AND a.highest = e.score) b
              ON s.student_id = b.student_id
       LEFT JOIN (SELECT DISTINCT student_id
                  FROM   Exam) c
              ON s.student_id = c.student_id
WHERE  b.student_id IS NULL
       AND c.student_id IS NOT NULL
ORDER  BY 1 ASC;

문제 링크

SELECT q.id,
       q.year,
       Ifnull(npv, 0) AS "npv"
FROM   Queries q
       LEFT JOIN NPV n
              ON q.id = n.id
                 AND q.year = n.year;

문제 링크
중간 구간('[10-15>')에 값이 비는 경우가 있기 때문에
case when 구문으로 한 번에 구하기는 어렵다.
union을 써서 전체 경우의 수를 모두 만들어 놓고
결과를 구한 테이블을 left join해서 값이 없을(null) 경우 0 처리까지 해 줘야 함.

SELECT a.bin,
       Ifnull(cnt, 0) AS "total"
FROM   (SELECT '[0-5>' AS "bin"
        UNION
        SELECT '[5-10>'
        UNION
        SELECT '[10-15>'
        UNION
        SELECT '15 or more') a
       LEFT JOIN (SELECT CASE
                           WHEN duration / 60 >= 0
                                AND duration / 60 < 5 THEN '[0-5>'
                           WHEN duration / 60 >= 5
                                AND duration / 60 < 10 THEN '[5-10>'
                           WHEN duration / 60 >= 10
                                AND duration / 60 < 15 THEN '[10-15>'
                           WHEN duration / 60 >= 15 THEN '15 or more'
                         end      AS "bin",
                         Count(*) AS "cnt"
                  FROM   Sessions
                  GROUP  BY 1) b
              ON a.bin = b.bin;