We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
This SQL query identifies symmetric pairs (X, Y) and (Y, X) by performing a self JOIN, ensuring that each pair appears only once using WHERE X ≤ Y. It also correctly handles reflexive pairs (X = Y), ensuring they are included only if they appear more than once in the table. The GROUP BY removes duplicates, and the ORDER BY X ASC ensures results are sorted properly.
"""
SELECT
f1.x, f1.y
FROM
functions AS f1
INNER JOIN
functions AS f2
ON
f1.x = f2.y
AND f2.x = f1.y
AND (
f1.x <> f1.y
OR (
SELECT count(*)
FROM functions f
WHERE f.x = f1.x AND f.y = f1.y
) > 1
)
WHERE
f1.x <= f1.y
GROUP BY
f1.x, f1.y
ORDER BY
f1.x ASC;
""""
Using the self JOIN ensures that we correctly match symmetric pairs (X, Y) and (Y, X), which a simple WHERE condition cannot achieve. The WHERE X ≤ Y condition prevents duplicate pairs from appearing in different orders. The reflexive pair check (X = Y) with COUNT(*) > 1 ensures that values like (30, 30) are included only if they appear more than once. The GROUP BY eliminates redundancies, and ORDER BY X ASC improves readability.
Cookie support is required to access HackerRank
Seems like cookies are disabled on this browser, please enable them to open this website
Symmetric Pairs
You are viewing a single comment's thread. Return to all comments →
This SQL query identifies symmetric pairs (X, Y) and (Y, X) by performing a self JOIN, ensuring that each pair appears only once using WHERE X ≤ Y. It also correctly handles reflexive pairs (X = Y), ensuring they are included only if they appear more than once in the table. The GROUP BY removes duplicates, and the ORDER BY X ASC ensures results are sorted properly. """ SELECT f1.x, f1.y FROM functions AS f1 INNER JOIN functions AS f2 ON f1.x = f2.y AND f2.x = f1.y AND ( f1.x <> f1.y OR ( SELECT count(*) FROM functions f WHERE f.x = f1.x AND f.y = f1.y ) > 1 ) WHERE f1.x <= f1.y GROUP BY f1.x, f1.y ORDER BY f1.x ASC;
"""" Using the self JOIN ensures that we correctly match symmetric pairs (X, Y) and (Y, X), which a simple WHERE condition cannot achieve. The WHERE X ≤ Y condition prevents duplicate pairs from appearing in different orders. The reflexive pair check (X = Y) with COUNT(*) > 1 ensures that values like (30, 30) are included only if they appear more than once. The GROUP BY eliminates redundancies, and ORDER BY X ASC improves readability.