Mysql 문제풀이 강의 해설 SET4

📌 Rank Scores
문제출처ㅣ https://leetcode.com/problems/rank-scores/

SELECT SCORE,
       DENSE_RANK() OVER (ORDER BY SCORE DESC) AS 'RANK'
FROM SCORES


Input: 
Scores table:
+----+-------+
| id | score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+
Output: 
+-------+------+
| score | rank |   > DENSE_RANK()
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

📌 Trips ans Users
문제출처 | https://leetcode.com/problems/trips-and-users/

SELECT T.REQUEST_AT AS 'DAY'
      ,ROUND(CANCEL_COUNT / TOTAL_COUNT,2) AS 'Cancellation Rate'
FROM(
	SELECT REQUEST_AT
       	  ,SUM(CASE WHEN STATUS != 'COMPLETED' THEN 1 ELSE 0 END) CANCEL_COUNT -- case문 피벗
          ,COUNT(*) TOTAL_COUNT
FROM TRIPS T 
        INNER JOIN USERS US ON T.CLIENT_ID = US.USERS_ID
        INNER JOIN USERS UD ON T.DRIVER_ID = UD.USERS_ID
WHERE T.REQUEST_AT BETWEEN '2013-10-01' AND '2013-10-03'
AND US.BANNED ='NO'
AND UD.BANNED ='NO'
GROUP BY REQUEST_AT ) T

📌 Exchange Seats
문제출저 | https://leetcode.com/problems/exchange-seats/

짝수인 얘들은 원래 자기 id-1, 홀수인 얘들은 원래 자기 id+1, 마지막에 있는 원래 id를 그대로 (id가 홀수로)

#전체 학생이 짝수라고 가정하고 풀기
#SELECT CASE WHEN MOD(ID,2) = 1 THEN ID+1
#ELSE ID-1
#END NEW_ID
#, ID
#, STUDENT
#FROM SEAT

#전체 학생이 홀수있을수도 있을 경우
SELECT CASE WHEN MOD(ID,2) =1 AND ID != TOTAL_ROWS THEN ID+1 
            WHEN MOD(ID,2) = 1 AND ID = TOTAL_ROWS THEN ID            
       ELSE ID -1 END ID
       , STUDENT
FROM (

SELECT ID 
       , STUDENT
       , COUNT(*) OVER () AS TOTAL_ROWS
FROM SEAT ) T
ORDER BY ID