A friend asked me a radio trivia question with a surprising answer: what U.S. city has the most licensed AM radio stations? (This question had apparently stumped him when a friend asked him, and now he was trying to stump me.) But since I have the contents of the FCC’s Common Database System loaded into a convenient PostgreSQL cluster, it was actually a pretty simple question to answer — albeit requiring some gnarly SQL. Here’s what I came up with (I wanted to see which were the top 10 communities, not just number one, which would have been easier):

SELECT * FROM (SELECT RANK() OVER (ORDER BY COUNT(*) DESC),
                      COUNT(*), comm_city, comm_state,
                      string_agg(fac_callsign, ', ' ORDER BY fac_callsign)
                  FROM facility
                  WHERE fac_country = 'US' AND fac_service = 'AM' 
                      AND fac_status IN ('LICEN', 'LICSL', 'CPOFF')
                  GROUP BY comm_city, comm_state) AS a
    WHERE rank <= 10
    ORDER BY rank, comm_state, comm_city

The aggregation function string_agg() is a PostgreSQL extension, as far as I know, but the rest is fairly standard SQL. Here’s the surprising result:

Note that I could have used ROW_NUMBER() instead of RANK() if I wanted exactly 10 rows in the output, but since there’s no way to prioritize one city over another with the same number of stations, this doesn’t produce meaningful (or consistent) results — and if I had wanted to do that anyway, using a LIMIT clause would have been much simpler. This way I got to learn the syntax of RANK(), and then when I had that working, I realized that I wanted to get a list of the stations, too, so I had to find out about string_agg() — and then I needed to learn about ORDER BY clauses in aggregation functions to get the results in a useful and stable order.

In case you were wondering, the city with the greatest number of licensed FM stations is Chicago, with 25, followed by Los Angeles and New York (20 each), then Anchorage and San Francisco (19 each). Philadelphia, Detroit, Las Vegas, Houston, Pittsburgh, Amarillo, and Seattle round out the top 10. For (full-power, digital) television, Honolulu is again the winner (12), followed by LA and Miami (11 each), then Phoenix, SF, Denver, Chicago, Oklahoma City, Atlanta, New Orleans, Albuquerque, Houston, and Milwaukee. The cities with the most FM translators are Kingman, Arizona, and Pocatello, Idaho, with 22 each; Lake Havasu City, Arizona, Ithaca, New York, and Grand Junction, Colorado, round out the top five. No city has more than five “class A” digital TV stations (Las Vegas), but St. James, Minnesota, has 24 regular digital LPTVs, and Lubbock, Texas, has 15 remaining analog LPTVs. All of these counts include stations that are currently silent, as far as the FCC knows; if silent stations (‘LICSL’ in the SQL query above) were subtracted, Honolulu would be tied with New York in the original trivia question.

You might wonder why Honolulu would have so many stations. It’s probably something to do with the fact that the entire island of Oahu is part of the city-and-county of Honolulu, and none of the other islands (counties in their own right) are close enough to Oahu to provide alternative communities of license for stations serving the Honolulu market. (Compare the Boston market, which is much larger in population, but has AM stations licensed to Quincy, Cambridge, Watertown, Revere, Lynn, Everett, Newton, Brookline, Dedham, and even-farther-outlying suburbs and exurbs. Once these were actual local stations, but they no longer serve any particular role in the communities they are actually licensed to serve.)