Rank Flights by Airline and Destination Frequency
Reported by candidates from Capital One's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
Capital One reported this one in September 2026, and it's a SQL question that looks like a sorting puzzle but is really a counting puzzle. The table might be empty, or it might be huge, and a per-row subquery that recounts flights for every row is the brute force that falls over. You need counts per airline and per airline-destination pair, attached to every flight row, then one big ORDER BY. It's window functions or a join to grouped counts. If you blank on the sort keys during the live OA, StealthCoder is the safety net sitting invisibly on your screen.
The problem
You are given a flights table. Return one output row for every flight. Output exactly the columns id, destination, and departure_time. Ordering Airlines with more total flights come first. If two airlines have the same total, order them by aviacompany in ascending order. Within an airline, destinations with more flights for that airline come first. If two destinations for the same airline have the same count, order them by destination in ascending order. Within an airline and destination, order rows by departure_time in ascending order. If all preceding sort keys tie, order rows by id in ascending order. Compute both flight counts from all rows in the input table. The duration column is not returned and does not affect the ordering. Tables flights: id PK (Integer), aviacompany (Text), destination (Text), departure_time (Text), duration (Integer) Constraints The id column uniquely identifies a flight. Every input cell is non-null. aviacompany and destination contain non-empty uppercase ASCII letters and spaces. departure_time is a zero-padded 24-hour time in HH-MM format. duration is an integer number of minutes. The flights table may be empty.
Reported by candidates. Source: FastPrep
Pattern and pitfall
The trick is computing two counts without collapsing rows. Use COUNT(*) OVER (PARTITION BY aviacompany) for the airline total and COUNT(*) OVER (PARTITION BY aviacompany, destination) for the pair count. Then ORDER BY airline total DESC, aviacompany ASC, pair count DESC, destination ASC, departure_time ASC, id ASC. Select only id, destination, departure_time. The common pitfall is grouping first and losing the one-row-per-flight output. Another is forgetting the tiebreaker on aviacompany, so two airlines with equal totals interleave their rows. You can order by window expressions directly or compute them in a CTE first. Don't sort by duration, it's a distractor. Nulls aren't a concern since every cell is non-null, and an empty table just returns nothing. StealthCoder is the hedge if you freeze on the six-key ordering live, since it reads the spec and hands you the query.
The honest play: practice the pattern, and have StealthCoder ready for the one you didn't see coming.
You can drill Rank Flights by Airline and Destination Frequency cold, or you can hedge it. StealthCoder runs invisibly during screen share and surfaces a working solution in under 2 seconds. The proctor sees the IDE. They don't see what's behind it. Built for the candidate who saw this exact problem leak two days before his OA and wondered if anyone had a play.
Get StealthCoderRelated leaked OAs
You've seen the question.
Make sure you actually pass Capital One's OA.
Capital One reuses patterns across OAs. Built for the candidate who saw this exact problem leak two days before his OA and wondered if anyone had a play. Works on HackerRank, CodeSignal, CoderPad, and Karat.
Rank Flights by Airline and Destination Frequency FAQ
How hard is this Capital One SQL question really?+
Easy to medium. There's no tricky join or recursion. The difficulty is keeping six sort keys in the right order and direction. If you know window functions with PARTITION BY, it's about ten minutes of careful typing and checking.
What's the trick to getting both counts on every row?+
Use COUNT(*) OVER with two different partitions: one by aviacompany, one by aviacompany and destination. Window functions keep every flight row, so you don't need to group and join back. A CTE with GROUP BY plus a join also works.
Do I need to worry about the duration column?+
No. The problem says duration isn't returned and doesn't affect ordering. Leave it out of SELECT and ORDER BY. It's there to tempt you into adding a sort key that isn't in the spec.
What about the empty table case?+
A standard SELECT with window functions returns zero rows on an empty table, which is the correct output. You don't need special handling. Just avoid anything that assumes at least one row, like a scalar subquery used in a comparison.
How do I prepare for this in 48 hours?+
Write the query twice, once with window functions and once with GROUP BY plus a join. Practice multi-key ORDER BY with mixed DESC and ASC. Check tie handling by hand on a tiny example with two airlines sharing the same total.