Symmetric Coordinates - Video Solutions

Problem Overview: You are given a table of coordinates. A pair of rows is considered symmetric if one row contains (x, y) and another row contains (y, x). The task is to return these symmetric coordinate pairs without duplicates.

Approach 1: Basic Self Join (O(n²) time, O(1) extra space)

Join the coordinates table with itself and look for rows where a.x = b.y and a.y = b.x. To avoid pairing a row with itself incorrectly, use a condition such as a.id < b.id or another unique column. This ensures each symmetric pair appears once. The database engine performs a comparison between rows, which conceptually behaves like O(n²) comparisons, though indexes can improve real performance.

This approach works well when the dataset is small and the schema already includes a unique identifier. The logic is straightforward: match reversed coordinates directly with a join condition.

Approach 2: Window Function + Self Join (O(n²) time, O(n) space)

Duplicate coordinate values can create tricky cases, especially when x = y. A coordinate like (5,5) is symmetric with itself only if it appears more than once. Window functions help here. First compute a frequency or row number using a window function such as COUNT(*) OVER(PARTITION BY x, y) or ROW_NUMBER(). Then perform the self join on reversed coordinates and filter duplicates using ordering conditions.

This method cleanly handles edge cases. When x = y, require the count of that coordinate to be greater than one. For general symmetric pairs, ensure you only return one direction by enforcing a consistent ordering condition such as a.x <= a.y or by comparing IDs.

Window functions reduce ambiguity when multiple identical rows exist. Modern SQL engines like MySQL 8+ optimize these operations efficiently, making this approach reliable for production queries involving moderate datasets.

Recommended for interviews: The window function + self join approach is the safest answer. The simple self join shows you understand the symmetry condition, but handling duplicates and x = y cases demonstrates deeper SQL skill. Interviewers expect candidates to combine joins with analytical functions when working with relational data. Review related concepts such as SQL, database querying, and window functions.