250216_TIL

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

SQL 문제풀이 복습

Leetcode

2314. The First Day of the Maximum Recorded Degree in Each City
풀이시간 01:36

SELECT city_id,
       day,
       degree
FROM   (SELECT city_id,
               day,
               degree,
               RANK()
                 OVER(
                   partition BY city_id
                   ORDER BY degree DESC, day ASC) AS "ranking"
        FROM   Weather) r
WHERE  ranking = 1
ORDER  BY 1;

2324. Product Sales Analysis IV
풀이시간 02:04

SELECT user_id,
       product_id
FROM   (SELECT user_id,
               s.product_id,
               Sum(quantity * price)                    AS "total",
               RANK()
                 OVER(
                   partition BY user_id
                   ORDER BY Sum(quantity * price) DESC) AS "ranking"
        FROM   Sales s
               JOIN Product p
                 ON s.product_id = p.product_id
        GROUP  BY 1,
                  2) r
WHERE  ranking = 1;

2329. Product Sales Analysis V
풀이시간 01:05

SELECT s.user_id,
       SUM(quantity * price) AS "spending"
FROM   Sales s
       JOIN Product p
         ON s.product_id = p.product_id
GROUP  BY 1
ORDER  BY 2 DESC,
          1 ASC;

2339. All the Matches of the League
풀이시간 01:04

SELECT t1.team_name AS "home_team",
       t2.team_name AS "away_team"
FROM   Teams t1,
       Teams t2
WHERE  t1.team_name <> t2.team_name;

2346. Compute the Rank as a Percentage
풀이시간 05:17

SELECT student_id,
       department_id,
       IFNULL(Round(100.0 * ( ranking - 1 ) / ( cnt - 1 ), 2), 0) AS
       "percentage"
FROM   (SELECT student_id,
               s.department_id,
               mark,
               RANK()
                 OVER(
                   partition BY department_id
                   ORDER BY mark DESC) AS "ranking",
               cnt
        FROM   Students s
               JOIN (SELECT department_id,
                            Count(DISTINCT student_id) AS "cnt"
                     FROM   Students
                     GROUP  BY 1) d
                 ON s.department_id = d.department_id) r;