Symmetric Pairs in SQL | HackerRank Solution

Hello coders, today we are going to solve Symmetric Pairs HackerRank Solution in SQL.

Problem

You are given a table, Functions, containing two columns: 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``````

Solution – Symmetric Pairs in SQL

MySQL

```SELECT X,
Y
FROM FUNCTIONS F1
WHERE EXISTS
(SELECT *
FROM FUNCTIONS F2
WHERE F2.Y = F1.X
AND F2.X = F1.Y
AND F2.X > F1.X)
AND (X != Y)
UNION
SELECT X,
Y
FROM FUNCTIONS F1
WHERE X = Y
AND (
(SELECT COUNT(*)
FROM FUNCTIONS
WHERE X = F1.X
AND Y = F1.X) > 1)
ORDER BY X;```

Disclaimer: The above Problem (Symmetric Pairs) is generated by Hacker Rank but the Solution is Provided by CodingBroz. This tutorial is only for Educational and Learning Purpose.

1 thought on “Symmetric Pairs in SQL | HackerRank Solution”

1. IN MS SQL
with cte as(SELECT X,Y, ROW_NUMBER() OVER(Partition by X ORDER BY X) rn
FROM Functions
WHERE X=Y
)
,cte1 as (SELECT X,Y
FROM cte
WHERE rn !=1 )
SELECT f1.X,f1.Y
FROM Functions f1
JOIN Functions f2
ON f1.X=f2.Y and f1.Y=f2.X
AND f1.X < f1.Y
UNION
SELECT X,Y
FROM cte1
ORDER BY X