I’m looking to create a SQL query that answers the following question: “Which was the most common destination airport in each month?” The table is structured such that it has Month and Destination_Airport columns (amongst other things), and I’m hoping for the query result to look like the following:
Month | Destination_Airport |
---|---|
1 | JFK |
2 | LAX |
3 | BOS |
4 | MIA |
and | so on |
I’ve tried this query SELECT Month, Destination_Airport FROM Flight GROUP BY Month, Destination_Airport ORDER BY COUNT(*) DESC
which gets me all of the Month, Destination_Airport pairs ordered by their frequency, but I don’t know how to trim it down to only return the most popular airport per month, and nothing more. Thanks in advance for any help!
Advertisement
Answer
You are on the right track. You can then use window functions to get the top per month:
SELECT md.* FROM (SELECT Month, Destination_Airport, COUNT(*) as cnt, ROW_NUMBER() OVER (PARTITION BY Month ORDER BY COUNT(*) DESC) as seqnum FROM Flight GROUP BY Month, Destination_Airport ) md WHERE seqnum = 1;