Identify Unique Unordered City Pairs in Flight Log

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Onsite rounds at Amazon.

Q: What role is this question designed for?

This question is commonly asked for Business Intelligence Engineer candidates at Amazon during technical interviews.

Question

FLIGHTS

+----------------+---------------+
| departure_city | arrival_city  |
+----------------+---------------+
| NYC            | LAX           |
| LAX            | NYC           |
| ORD            | ATL           |
+----------------+---------------+

##### Scenario

You work with an airline flight log. Each row records a flight’s origin and destination city. Management wants to see all distinct city pairs regardless of flight direction (NYC–LAX is the same as LAX–NYC).

##### Question

Write a SQL query (or equivalent Python/Pandas snippet) that returns the unique unordered city pairs from the FLIGHTS table, treating (A,B) and (B,A) as the same route combination.

##### Hints

Normalize the order of the two cities—e.g., use LEAST/GREATEST or sort values—then GROUP BY the normalized pair.

PracHub · Accepted Answer

This question evaluates competency in data manipulation, deduplication of symmetric relationships, and handling unordered pairs within relational or tabular datasets using SQL or Python/Pandas.

Quick Overview

Identify Unique Unordered City Pairs in Flight Log