250210_TIL

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

SQL 문제풀이 복습

---

Leetcode

1988. Find Cutoff Score for Each School
풀이시간 04:37

SELECT school_id,
       IFNULL(score, -1) AS "score"
FROM   (SELECT school_id,
               score,
               RANK()
                 OVER(
                   partition BY school_id
                   ORDER BY student_count DESC, score ASC) AS "ranking"
        FROM   Schools s
               LEFT JOIN Exam e
                      ON s.capacity >= e.student_count) result
WHERE  ranking = '1';

1990. Count the Number of Experiments
풀이시간 07:29

SELECT c.platform,
       c.experiment_name,
       IFNULL(cnt, 0) AS "num_experiments"
FROM   (SELECT platform,
               experiment_name
        FROM   (SELECT 'IOS' AS "platform"
                UNION
                SELECT 'Android'
                UNION
                SELECT 'Web') a,
               (SELECT 'Reading' AS "experiment_name"
                UNION
                SELECT 'Sports'
                UNION
                SELECT 'Programming') b) c
       LEFT JOIN (SELECT platform,
                         experiment_name,
                         COUNT(*) AS "cnt"
                  FROM   Experiments
                  GROUP  BY 1,
                            2) e
              ON c.platform = e.platform
                 AND c.experiment_name = e.experiment_name;

2004. The Number of Seniors and Juniors to Join the Company
풀이시간 09:13

WITH senior
     AS (SELECT employee_id,
                'Senior' AS "experience",
                salary,
                total_senior
         FROM   (SELECT employee_id,
                        salary,
                        Sum(salary)
                          OVER(
                            ORDER BY salary ASC) AS "total_senior"
                 FROM   Candidates
                 WHERE  experience = 'Senior') ts
         WHERE  total_senior <= 70000),
     junior
     AS (SELECT employee_id,
                'Junior' AS "experience",
                salary,
                total_junior
         FROM   (SELECT employee_id,
                        salary,
                        Sum(salary)
                          OVER(
                            ORDER BY salary ASC) AS "total_junior"
                 FROM   Candidates
                 WHERE  experience = 'Junior') tj
         WHERE  total_junior <= 70000 - (SELECT IFNULL(Max(total_senior), 0)
                                         FROM   senior))
SELECT a.experience,
       Count(IFNULL(s.employee_id, j.employee_id)) AS "accepted_candidates"
FROM   (SELECT 'Senior' AS "experience"
        UNION
        SELECT 'Junior') a
       LEFT JOIN senior s
              ON a.experience = s.experience
       LEFT JOIN junior j
              ON a.experience = j.experience
GROUP  BY 1;

2010. The Number of Seniors and Juniors to Join the Company II
풀이시간 03:47

WITH senior
     AS (SELECT employee_id,
                'Senior' AS "experience",
                total_senior
         FROM   (SELECT employee_id,
                        Sum(salary)
                          OVER(
                            ORDER BY salary) AS "total_senior"
                 FROM   Candidates
                 WHERE  experience = 'Senior') ts
         WHERE  total_senior <= 70000),
     junior
     AS (SELECT employee_id,
                'Junior' AS "experience"
         FROM   (SELECT employee_id,
                        Sum(salary)
                          OVER(
                            ORDER BY salary) AS "total_junior"
                 FROM   Candidates
                 WHERE  experience = 'Junior') tj
         WHERE  total_junior <= 70000 - (SELECT IFNULL(Max(total_senior), 0)
                                         FROM   senior))
SELECT employee_id
FROM   senior
UNION ALL
SELECT employee_id
FROM   junior;

2020. Number of Accounts That Did Not Stream
풀이시간 03:23

SELECT COUNT(DISTINCT s.account_id) AS "accounts_count"
FROM   Streams s
       JOIN (SELECT account_id
             FROM   Subscriptions
             WHERE  YEAR(start_date) = '2021'
                     OR YEAR(end_date) = '2021') a
         ON s.account_id = a.account_id
WHERE  YEAR(stream_date) <> '2021';

---

1972. First and Last Call On the Same Day
내일 다시 한 번 더 풀어보자.
아직도 이해가 잘 안 되는 부분이 있음.

WITH a
     AS (SELECT caller_id    AS "user1",
                recipient_id AS "user2",
                call_time
         FROM   calls
         UNION ALL
         SELECT recipient_id AS "user1",
                caller_id    AS "user2",
                call_time
         FROM   calls),
     b
     AS (SELECT user1,
                user2,
                Rank()
                  OVER(
                    partition BY user1, Date(call_time)
                    ORDER BY call_time)      AS "first",
                Rank()
                  OVER(
                    partition BY user1, Date(call_time)
                    ORDER BY call_time DESC) AS "last"
         FROM   a)
SELECT DISTINCT b1.user1 AS "user_id"
FROM   b b1
       JOIN b b2
         ON b1.user1 = b2.user1
            AND b1.user2 = b2.user2
            AND b1.first = 1
            AND b2.last = 1;