Given a schema of flight bookings and passenger details, write a query to find the top three routes by total ancillary revenue generated.
Use confirmed bookings, posted ancillary transactions, and bookings associated with a passenger record. Treat NULL ancillary amounts as zero.
origin_airport, destination_airport, total_ancillary_revenue, and route_rank.route_rank ascending, breaking ties by origin and destination airport codes.| Column | Type | Description |
|---|---|---|
| booking_idPK | INT | Unique booking identifier |
| passenger_id | VARCHAR(12) | Passenger associated with the booking |
| origin_airport | VARCHAR(3) | Origin airport code |
| destination_airport | VARCHAR(3) | Destination airport code |
| booking_status | VARCHAR(20) | Current booking status |
| Column | Type | Description |
|---|---|---|
| passenger_idPK | VARCHAR(12) | Unique passenger identifier |
| full_name | VARCHAR(100) | Passenger name |
| passenger_status | VARCHAR(20) | Passenger record status |
| Column | Type | Description |
|---|---|---|
| ancillary_idPK | INT | Unique ancillary transaction identifier |
| booking_id | INT | Booking receiving the ancillary purchase |
| ancillary_amount | NUMERIC(10,2) | Ancillary revenue amount |
| transaction_status | VARCHAR(20) | Processing status of the transaction |