[HackerRank] Symmetric Pairs

20 20이 self join하는 예외 케이스 나눠서 풀기 !

 

Question

You are given a table, Functions, containing two columns: X and Y.

Two pairs (X1, Y1) and (X2, Y2) are said to be symmetric pairs if X1 = Y2 and X2 = Y1.

Write a query to output all such symmetric pairs in ascending order by the value of X. List the rows such that X1 ≤ Y1.

Sample Input

Sample Output

20 20
20 21
22 23

 

Point

 

• 20 20이 하나만 있을 때 서로 self join해서 20 20 20 20 이 되는 문제점 존재.
  • -> X,Y가 같을 때 케이스를 따로 group by - count를 이용해서 2개 이상일 때 출력.

 

Code (After 250821)

 

with base as(
    select x, y
    from Functions
    where (x, y) not in (select x, y 
                            from functions
                            where x = y
                            group by 1, 2
                            having count(*) = 1
                        )
)
select distinct a.x, a.y

from base a
join base b on a.x = b.y and a.y = b.x
where a.x <= a.y
order by 1

 

Code (Before)

 

SELECT X, Y -- 20 20이 하나만 있을 때 SELF JOIN 되는 케이스 방지.
FROM FUNCTIONS
WHERE X = Y
GROUP BY X, Y
HAVING COUNT(*) >= 2

UNION

SELECT F1.X, F1.Y
FROM FUNCTIONS F1
JOIN FUNCTIONS F2 ON (F1.X = F2.Y) AND (F1.Y = F2.X)
WHERE F1.X < F1.Y

ORDER BY 1

 

now me