240826_TIL

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

SQL 코드카타

문제 링크

SELECT e.user_id
FROM   emails e
       LEFT JOIN texts t
              ON e.email_id = t.email_id
                 AND Datediff(action_date, signup_date) = 1
WHERE  signup_action = 'Verified'
ORDER  BY 1;

문제 링크

SELECT a.student_id
FROM   students a
       LEFT JOIN courses b
              ON a.major = b.major
       LEFT JOIN enrollments c
              ON a.student_id = c.student_id
                 AND b.course_id = c.course_id
GROUP  BY 1
HAVING Count(DISTINCT b.course_id) = Count(DISTINCT c.course_id)
       AND Max(c.grade) = 'A'
ORDER  BY 1;

문제 링크
와 드럽게 헷갈리네 진짜;;
그래도 어떻게 풀긴 풀었다.

WITH a
AS
  (
             SELECT     student_id,
                        s.major,
                        c.course_id,
                        c.mandatory
             FROM       students s
             INNER JOIN courses c
             ON         s.major = c.major),
  result
AS
  (
            SELECT    a.student_id,
                      a.course_id,
                      a.mandatory,
                      e.grade,
                      e.gpa
            FROM      a
            LEFT JOIN enrollments e
            ON        a.student_id = e.student_id
            AND       a.course_id = e.course_id
            ORDER BY  1,
                      2),
  #첫째 조건(모든 전공 다 수강 + 선택과목 최소 2개)
  cte1
AS
  (
           SELECT   student_id,
                    count(DISTINCT
                    CASE
                             WHEN mandatory='yes' THEN course_id
                    end) AS "cnt_mandatory",
                    count(DISTINCT
                    CASE
                             WHEN mandatory='no' THEN course_id
                    end) AS "cnt_non_mandatory",
                    count(DISTINCT
                    CASE
                             WHEN mandatory='yes'
                             AND      grade IS NOT NULL THEN course_id
                    end) AS "cnt_enroll_man",
                    count(DISTINCT
                    CASE
                             WHEN mandatory='no'
                             AND      grade IS NOT NULL THEN course_id
                    end) AS "cnt_enroll_nonman"
           FROM     result
           GROUP BY 1
           HAVING   cnt_mandatory = cnt_enroll_man
           AND      cnt_enroll_nonman >= 2),
  #둘째 조건-1(모든 필수과목이 a)
  cte2
AS
  (
           SELECT   student_id,
                    count(DISTINCT
                    CASE
                             WHEN mandatory='yes' THEN course_id
                    end) AS "cnt_mandatory",
                    count(DISTINCT
                    CASE
                             WHEN mandatory='yes'
                             AND      grade='A' THEN course_id
                    end) AS "cnt_man_a"
           FROM     result
           GROUP BY 1
           HAVING   cnt_mandatory = cnt_man_a),
  #둘째 조건-2(모든 선택과목이 b이상)
  cte3
AS
  (
           SELECT   student_id,
                    count(course_id) AS "overb_nonmandatory"
           FROM     result
           WHERE    mandatory = 'no'
           AND      grade IN ('A',
                              'B')
           GROUP BY 1
           HAVING   count(DISTINCT grade) <= 2),
  #셋째 조건(전체 평점 2.5 이상)
  cte4
AS
  (
           SELECT   student_id,
                    avg(gpa) AS "overall"
           FROM     enrollments
           GROUP BY 1
           HAVING   overall >= 2.5)
  SELECT     cte1.student_id
  FROM       cte1
  INNER JOIN cte2
  ON         cte1.student_id = cte2.student_id
  INNER JOIN cte3
  ON         cte2.student_id = cte3.student_id
  INNER JOIN cte4
  ON         cte3.student_id = cte4.student_id
  ORDER BY   1;

문제 링크
오랜만에 group_concat을 써 봤다.

예전 코드카타에서도 써본 적이 있었는데,
여러 행으로 나누어져 있는 값들을 하나의 값을 기준으로 그루핑한 후
값들을 한꺼번에 concat해 주는 함수다.

이 때 separator 인자로 ',' 등을 줄 수 있고
(이 문제에서는 , 뒤에 띄어쓰기가 들어가야 함)
아무것도 넣지 않으면 자동으로 ,가 들어간다.

이 문제에서는 city끼리 뭉쳐질 때 city의 알파벳 순으로
오름차순이 되어야 하기 때문에 order by를 써 주었고,
, 다음에 공백이 있어야 하므로 separator 인자에 ', '을 써 주었다.

아래는 정답 쿼리.

SELECT state,
       Group_concat(city ORDER BY city SEPARATOR ', ') AS "cities"
FROM   cities
GROUP  BY 1
ORDER  BY 1; 

문제 링크
바이너리 연산에 관한 문제.

permissions를 이진수로 바꾼 다음
모든 자릿수가 0이나 1로 공통되어 있으면 1, 아니면 0을 반환하는데,
bit_or를 쓰게 되면 1111이 되므로 8+4+2+1 = 15가 되는 식이다.

이건 그냥 이런 게 있나보다 하고 넘어가자.

SELECT Bit_and(permissions) AS common_perms,
       Bit_or(permissions)  AS any_perms
FROM   user_permissions;