Question

2978. Symmetric Coordinates

中文文档

Description

Table: Coordinates

+-------------+------+
| Column Name | Type |
+-------------+------+
| X       | int  |
| Y       | int  |
+-------------+------+
Each row includes X and Y, where both are integers. Table may contain duplicate values.

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

Write a solution that outputs, among all these symmetric coordintes, only those unique coordinates that satisfy the condition X1 <= Y1.

Return _the result table ordered by _X _and _ Y _(respectively)_ _in ascending order_.

The result format is in the following example.

 

Example 1:

Input: 
Coordinates table:
+----+----+
| X  | Y  |
+----+----+
| 20 | 20 |
| 20 | 20 |
| 20 | 21 |
| 23 | 22 |
| 22 | 23 |
| 21 | 20 |
+----+----+
Output: 
+----+----+
| x  | y  |
+----+----+
| 20 | 20 |
| 20 | 21 |
| 22 | 23 |
+----+----+
Explanation: 
- (20, 20) and (20, 20) are symmetric coordinates because, X1 == Y2 and X2 == Y1. This results in displaying (20, 20) as a distinctive coordinates.
- (20, 21) and (21, 20) are symmetric coordinates because, X1 == Y2 and X2 == Y1. However, only (20, 21) will be displayed because X1 <= Y1.
- (23, 22) and (22, 23) are symmetric coordinates because, X1 == Y2 and X2 == Y1. However, only (22, 23) will be displayed because X1 <= Y1.
The output table is sorted by X and Y in ascending order.

Solutions

Solution 1: Window Function + Self Join

We can use the window function ROW_NUMBER() to add an auto-incrementing sequence number to each row. Then, we perform a self join on the two tables, with the join conditions being p1.x = p2.y AND p1.y = p2.x AND p1.x <= p1.y AND p1.id != p2.id. Finally, we sort and remove duplicates.

# Write your MySQL query statement below
WITH
  P AS (
    SELECT
      ROW_NUMBER() OVER () AS id,
      x,
      y
    FROM Coordinates
  )
SELECT DISTINCT
  p1.x,
  p1.y
FROM
  P AS p1
  JOIN P AS p2 ON p1.x = p2.y AND p1.y = p2.x AND p1.x <= p1.y AND p1.id != p2.id
ORDER BY 1, 2;