241116_TIL

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

SQL 문제풀이 복습

Leetcode

문제 링크
cross join + where절

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;

문제 링크
window 함수 + join + 집계함수.

서브쿼리 중 제일 마지막 하나는 없어도 되지만
가독성을 생각하면 들어가는 게 나을 듯.
window함수의 연산 결과를 바로 계산에 쓰면 헷갈리니.

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

문제 링크
걍 집계함수.

SELECT teacher_id,
       COUNT(DISTINCT subject_id) AS "cnt"
FROM   Teacher
GROUP  BY 1;