Sort Flights by Company Volume and Per-Destination Flight Counts
Company: Capital One
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Online Assessment
A table holds flight records, one row per flight. Write a query that lists every flight with only three columns, `id`, `destination` and `departure_time`, sorted as follows:
1. By the total number of flights operated by the flight's company, in descending order.
2. On ties, by the company name in alphabetical order.
3. Then by the number of flights that the same company operates to the flight's destination, in descending order.
4. On ties, by the destination code in alphabetical order.
5. Then by departure time, earliest first.
6. Finally, by `id` in ascending order.
The report lists the first three keys and breaks off without giving the direction of the third key or any later key. This version makes the third key descending, like the first, and adds keys 4 to 6 so that every flight has exactly one position.
### Input Table(s)
**`flights`**: one row per flight; primary key `id`.
| Column | Type | Meaning |
| --- | --- | --- |
| `id` | INTEGER | Unique ID of the flight record |
| `aviacompany` | TEXT | Company operating the flight |
| `destination` | TEXT | Destination code of the flight |
| `departure_time` | TEXT | Departure time in `HH-MM` format |
| `duration` | INTEGER | Duration of the flight in minutes |
### Output Contract
Write one read-only PostgreSQL query (`SELECT`, optionally with `WITH`) that returns one row per flight, with exactly these columns in this order:
| Column | Type | Meaning |
| --- | --- | --- |
| `id` | integer | The flight's ID |
| `destination` | text | The flight's destination code |
| `departure_time` | text | The flight's departure time, unchanged |
The rows must appear in the order defined by keys 1 to 6. The last key, `id`, is unique, so the order is fully determined. The counts used for sorting must not appear as output columns.
### Example
`flights`:
| id | aviacompany | destination | departure_time | duration |
| --- | --- | --- | --- | --- |
| 1 | Delta | JFK | 08-30 | 300 |
| 2 | United | SFO | 07-15 | 360 |
| 3 | Delta | LAX | 12-00 | 330 |
| 4 | Alaska | SEA | 09-45 | 150 |
| 5 | United | ORD | 06-00 | 180 |
| 6 | Delta | JFK | 06-10 | 300 |
| 7 | Alaska | SEA | 21-05 | 150 |
| 8 | United | ORD | 18-20 | 180 |
| 9 | Alaska | JFK | 13-30 | 330 |
| 10 | Delta | LAX | 15-40 | 330 |
| 11 | United | ORD | 06-00 | 185 |
Expected result:
| id | destination | departure_time |
| --- | --- | --- |
| 6 | JFK | 06-10 |
| 1 | JFK | 08-30 |
| 3 | LAX | 12-00 |
| 10 | LAX | 15-40 |
| 5 | ORD | 06-00 |
| 11 | ORD | 06-00 |
| 8 | ORD | 18-20 |
| 2 | SFO | 07-15 |
| 4 | SEA | 09-45 |
| 7 | SEA | 21-05 |
| 9 | JFK | 13-30 |
- Delta and United each operate 4 flights and Alaska operates 3, so Delta's flights come first (Delta is alphabetically before United), then United's, then Alaska's.
- Delta flies to JFK twice and to LAX twice. The tie goes to JFK alphabetically, and within each destination the flights are ordered by departure time.
- United flies to ORD three times and to SFO once, so ORD comes first. Flights 5 and 11 both leave at 06-00, so `id` decides between them.
- Alaska flies to SEA twice and to JFK once, so SEA comes first. The destination count is per company: Delta's two JFK flights do not raise Alaska's JFK count.
### Constraints and Clarifications
- No column contains NULL, and `id` is unique, so there are no duplicate rows.
- Company names contain only letters, an uppercase first letter followed by lowercase letters, so plain ascending string order is alphabetical order. Destination codes are three uppercase letters.
- `departure_time` is a zero-padded 24-hour time such as `06-05` or `21-30`, so ascending text order is chronological order.
- The destination count in key 3 includes only flights of the same company to that destination; other companies' flights to the same destination do not add to it.
- `duration` plays no role in the result.
- Every flight appears exactly once. An empty table gives an empty result.
```hint Counts beside each flight
Both counts describe a group the flight belongs to, yet the output still has one row per flight.
```
Overview: SQL practice from a data science online assessment: list flight records sorted by how many flights each company operates, then by how many flights that company sends to each destination, with alphabetical and departure-time tie-breakers. Tests group counts attached to individual rows and multi-level ORDER BY.
