250212_TIL

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

SQL 문제풀이 복습

---

Leetcode

2026. Low-Quality Problems
풀이시간 01:36

SELECT problem_id
FROM   (SELECT problem_id,
               likes,
               dislikes
        FROM   Problems
        GROUP  BY 1
        HAVING likes / ( likes + dislikes ) < 0.6) r
ORDER  BY 1;

2041. Accepted Candidates From the Interviews
풀이시간 02:12

SELECT c.candidate_id
FROM   Candidates c
       JOIN (SELECT interview_id,
                    SUM(score) AS "total"
             FROM   Rounds
             GROUP  BY 1
             HAVING total > 15) i
         ON c.interview_id = i.interview_id
WHERE  years_of_exp >= 2;

2051. The Category of Each Member in the Store
풀이시간 05:19

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

2066. Account Balance
풀이시간 03:18

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

2072. The Winner University
풀이시간 03:31

SELECT CASE
         WHEN N > C THEN "New York University"
         WHEN N < C THEN "California University"
         ELSE "No Winner"
       end AS "winner"
FROM   (SELECT COUNT(DISTINCT CASE
                                WHEN n.score >= 90 THEN n.student_id
                              end) AS "N",
               COUNT(DISTINCT CASE
                                WHEN c.score >= 90 THEN c.student_id
                              end) AS "C"
        FROM   NewYork n,
               California c) result;

---

다시 풀어볼 문제

1972. First and Last Call On the Same Day
이 문제는 잠시 포기ㅠ 왜 이해가 안 되지..