Identify Unique Unordered City Pairs in Flight Log
Company: Amazon
Role: Business Intelligence Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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.
Overview: 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.
You work with an airline flight log. Each row in the FLIGHTS table 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). Write a SQL query that returns the unique unordered city pairs from the FLIGHTS table, treating (A,B) and (B,A) as the same route. Normalize the pair so that each route is represented in a consistent order.
Tables
FLIGHTS(departure_city VARCHAR(3), arrival_city VARCHAR(3))
Hints
- Normalize the pair order using LEAST and GREATEST (or equivalent CASE logic).
- Use DISTINCT (or GROUP BY) on the normalized pair to remove direction duplicates.