HackerRank: SQL 풀이(51~55)

51. Basic Join: Contest Leaderboard

WITH MaxScore AS (
                  SELECT hacker_id, challenge_id, MAX(score) AS max_score
                  FROM Submissions 
                  GROUP BY hacker_id, challenge_id
                  )

SELECT H.hacker_id, H.name, SUM(M.max_score) AS total_score
FROM Hackers H
INNER JOIN MaxScore M ON H.hacker_id = M.hacker_id
GROUP BY H.hacker_id, H.name
HAVING SUM(M.max_score) != 0
ORDER BY total_score DESC, H.hacker_id ASC; 

아 ;; HackerRank는 다른게 문제가 아니라, 지문이 영어임 ;;; 휴 ;; 풀었다

52. Advanced Join: Placements

SELECT S.Name
FROM Students S
INNER JOIN Packages P1 ON S.ID = P1.ID
INNER JOIN Friends F ON S.ID = F.ID
INNER JOIN Packages P2 ON F.Friend_ID = P2.ID
WHERE P2.Salary > P1.Salary
ORDER BY P2.Salary;

53. Advanced Join: Symmetric Pairs

• 틀린답

WITH CJ AS (
            SELECT F1.X AS X1, F1.Y AS Y1, F2.X AS X2, F2.Y AS Y2
            FROM Functions F1
            CROSS JOIN Functions F2
            )
SELECT X1, Y1
FROM CJ
WHERE X1 = Y2
AND X2 = Y1
AND X1 <= Y1
ORDER BY X1 ASC;

아니,,, 이렇게 카타시안 곱 때려버리면 없던 X Y 쌍도 생긴다구,,,
이렇게 하지 말구 원래 주어진 Functions 테이블 안에서 각기 다른 두 점에 대한 대칭쌍을 찾으라..! 이말이야
근데 이건 문제 말이 좀 어렵다...!
일단 자기 좌표를 제외하고 다른 좌표와 대칭쌍이어야 하니까... 그점을 놓치면 못 품.

• 또 틀림

SELECT F1.X, F1.Y
FROM Functions F1, Functions F2
WHERE F1.X = F2.Y
AND F2.X = F1.Y
AND (F1.X, F1.Y) <> (F2.X, F2.Y)
AND F1.X <= F1.Y
ORDER BY F1.X;

아니 ;; 튜플같은 비교 없어요 ~ 이거 RN으로 비교해야합니다~

• 또 틀림

WITH XY_RN AS ( 
                SELECT X, Y, ROW_NUMBER () OVER (ORDER BY X) AS RN
                FROM Functions
                )
SELECT F1.X, F1.Y
FROM XY_RN F1, XY_RN F2
WHERE F1.X = F2.Y
AND F2.X = F1.Y
AND F1.X <= F1.Y
AND F1.RN <> F2.RN
ORDER BY F1.X;

이건 ;; 중복제거를 안했음 ;; 허허

• 정답

WITH XY_RN AS ( 
                SELECT X, Y, ROW_NUMBER () OVER (ORDER BY X) AS RN
                FROM Functions
                )
SELECT DISTINCT F1.X, F1.Y
FROM XY_RN F1, XY_RN F2
WHERE F1.X = F2.Y
AND F2.X = F1.Y
AND F1.X <= F1.Y
AND F1.RN <> F2.RN
ORDER BY F1.X;