241107_TIL

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

SQL 문제풀이 복습

---

Leetcode

문제 링크
1트(7/27) 때는 공연히 복잡하게 풀었던 거 같다.

아예 방문한 적이 없는 경우에는
분모가 0으로 잡히므로 conversion rate를 계산했을 때 null이 뜨고,
방문한 적은 있지만 구매한 적이 없을 경우에는
conversion rate가 0이 나오는 게 포인트.

SELECT member_id,
       name,
       CASE
         WHEN conversion_rate >= 80 THEN 'Diamond'
         WHEN conversion_rate >= 50
              AND conversion_rate < 80 THEN 'Gold'
         WHEN conversion_rate < 50 THEN 'Silver'
         ELSE 'Bronze'
       END AS "category"
FROM   (SELECT m.member_id,
               m.name,
               100.0 * COUNT(p.visit_id) / COUNT(v.visit_id) AS
               "conversion_rate"
        FROM   Members m
               LEFT JOIN Visits v
                      ON m.member_id = v.member_id
               LEFT JOIN Purchases p
                      ON v.visit_id = p.visit_id
        GROUP  BY 1,
                  2) result;

문제 링크
1트(7/28) 때는 Deposit과 Withdraw에 따라 amount의 부호가 바뀌는 걸
cte에서 먼저 case when을 써서 처리하고,
그 뒤에 본 쿼리에서 window함수를 써서 계산했다.

하지만 window함수를 쓸 때
이렇게 한번에 case when까지 넣어서 쓸 수 있다.
집계함수와 case when을 결합해서 푸는 건 많이 해봤지만
window함수와도 결합할 수 있다는 점을 기억해 두기.

SELECT account_id,
       day,
       Sum(CASE
             WHEN type = 'Deposit' THEN amount
             WHEN type = 'Withdraw' THEN -amount
           END)
         OVER(
           PARTITION BY account_id
           ORDER BY day) AS "balance"
FROM   Transactions;

문제 링크
case when과 cross join을 섞어서 푸는 문제.
cross join에 들어갈 2개의 테이블을 cte로 만들어도 상관없다.

1트 때는 cte를 쓴 것 외에도 where절을 써서 90점 이상인 학생만 추린 후
id를 셌는데, 이번에는 count와 case when을 조합해서 바로 구했다.

전체적으로 case when을 이리저리 써 볼 수 있는 좋은 문제.

SELECT CASE
         WHEN exc_n > exc_c THEN 'New York University'
         WHEN exc_n < exc_c THEN 'California University'
         WHEN exc_n = exc_c THEN 'No Winner'
       END AS "winner"
FROM   (SELECT COUNT(CASE
                       WHEN score >= 90 THEN student_id
                     END) AS "exc_n"
        FROM   NewYork) N,
       (SELECT COUNT(CASE
                       WHEN score >= 90 THEN student_id
                     END) AS "exc_c"
        FROM   California) C;

---

프로그래머스

문제 링크

SELECT COUNT(DISTINCT ANIMAL_ID) AS "count"
FROM   ANIMAL_INS;

문제 링크
예전 포스팅에서도 비슷한 언급을 한 적이 있는 것 같은데,
엄밀히 말하면 이 쿼리는 오답이 되어야 한다.
가장 먼저 들어온 동물이 동시에 2마리 이상이 될 수도 있으므로.
(아니면 DATETIME 컬럼끼리 겹치는 경우는 없다고 문제에서 명시하든가)

SELECT NAME
FROM   ANIMAL_INS
ORDER  BY DATETIME
LIMIT  1;

이렇게 풀면 조금 더 모호한 부분을 줄일 수 있다.

SELECT NAME
FROM   (SELECT NAME,
               RANK()
                 OVER(
                   ORDER BY DATETIME) AS "ranking"
        FROM   ANIMAL_INS) result
WHERE  ranking = 1;

문제 링크

SELECT ANIMAL_ID,
       NAME,
       DATETIME
FROM   ANIMAL_INS
ORDER  BY 2,
          3 DESC;

문제 링크

SELECT ANIMAL_ID,
       NAME
FROM   ANIMAL_INS
ORDER  BY 1;

문제 링크

SELECT ANIMAL_ID,
       NAME
FROM   ANIMAL_INS
WHERE  ANIMAL_TYPE = 'Dog'
       AND NAME LIKE '%el%'
ORDER  BY 2;