Axis-Aligned Rectangle Opposite-Corner Areas

An operations analytics team stores geometric checkpoints as labeled 2D coordinates.

Table: points

• id: unique point identifier
• x_value: x coordinate
• y_value: y coordinate

A pair of points can represent opposite corners of an axis-aligned rectangle when:

• Their x coordinates are different, and
• Their y coordinates are different.

For every valid pair, compute:

• area = ABS(x1 - x2) * ABS(y1 - y2)

Task: Return every valid opposite-corner pair and its rectangle area.

Output requirements:

• Return exactly these columns in this order:
  1. p1
  2. p2
  3. area
• Report each unordered pair once using p1 < p2.
• Keep only rows with non-zero area.
• Order by area descending, then p1 ascending, then p2 ascending.

Supported submission environments:

• SQL (SQLite 3.27.2)
• Pandas (Python)

points:
| id | x_value | y_value |
|----|---------|---------|
| 1  | 2       | 7       |
| 2  | 4       | 8       |
| 3  | 2       | 10      |

[
  {"p1":2,"p2":3,"area":4},
  {"p1":1,"p2":2,"area":2}
]

points:
| id | x_value | y_value |
|----|---------|---------|
| 10 | 0       | 0       |
| 11 | 2       | 1       |
| 12 | 1       | 3       |
| 13 | 3       | 2       |

[
  {"p1":10,"p2":13,"area":6},
  {"p1":10,"p2":12,"area":3},
  {"p1":10,"p2":11,"area":2},
  {"p1":11,"p2":12,"area":2},
  {"p1":12,"p2":13,"area":2},
  {"p1":11,"p2":13,"area":1}
]

points:
| id | x_value | y_value |
|----|---------|---------|
| 21 | 5       | 1       |
| 22 | 5       | 4       |
| 23 | 5       | 9       |
| 24 | 5       | 20      |

[]