State City Roster with Initial-Letter Match

Medium

A civic analytics team maintains a two-column lookup table that records which cities belong to which states. Table: Cities(state, city) The pair (state, city) is unique. Your task is to build a filtered state-level summary. For each state, compute: cities: all city names in that state, concatenated into one string and sorted alphabetically, separated by ", " matching_letter_count: how many city names begin with the same first letter as the state name (compare case-insensitively) Include only states that satisfy both: the state has at least 3 cities matching_letter_count is greater than 0 Return columns in this exact order: state cities matching_letter_count Sort the final result by: matching_letter_count descending state ascending Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Cities:
| state    | city       |
|----------|------------|
| Asteria  | Afton      |
| Asteria  | Arbor      |
| Asteria  | Apex       |
| Asteria  | Bayview    |
| Borealis | Brookline  |
| Borealis | Bridgeport |
| Borealis | Belltown   |
| Borealis | Cedarfield |
| Cascade  | Dover      |
| Cascade  | Elmwood    |
| Cascade  | Fairview   |

Output

[
  {"state":"Asteria","cities":"Afton, Apex, Arbor, Bayview","matching_letter_count":3},
  {"state":"Borealis","cities":"Belltown, Bridgeport, Brookline, Cedarfield","matching_letter_count":3}
]

Explanation

Cascade has enough cities but no city starts with C, so it is excluded.

Example 2

Input

Cities:
| state    | city       |
|----------|------------|
| Elmstate | Eden       |
| Elmstate | Eastgate   |
| Elmstate | Everly     |
| Elmstate | Harborview |
| Fjord    | Falcon     |
| Fjord    | Flint      |
| Fjord    | Forest     |
| Fjord    | Granite    |
| Galena   | Grove      |
| Galena   | Garden     |
| Galena   | Hillside   |

Output

[
  {"state":"Elmstate","cities":"Eastgate, Eden, Everly, Harborview","matching_letter_count":3},
  {"state":"Fjord","cities":"Falcon, Flint, Forest, Granite","matching_letter_count":3},
  {"state":"Galena","cities":"Garden, Grove, Hillside","matching_letter_count":2}
]

Explanation

Elmstate and Fjord tie on matching count, so state name ascending is used.

Example 3

Input

Cities:
| state   | city      |
|---------|-----------|
| nova    | Nexus     |
| nova    | Nadir     |
| nova    | Northbay  |
| nova    | Aurora    |
| Orchid  | Opal      |
| Orchid  | Olive     |
| Orchid  | Maple     |
| Prairie | Pine      |
| Prairie | Quartz    |

Output

[
  {"state":"nova","cities":"Aurora, Nadir, Nexus, Northbay","matching_letter_count":3},
  {"state":"Orchid","cities":"Maple, Olive, Opal","matching_letter_count":2}
]

Explanation

Initial-letter matching is case-insensitive, so lowercase state names still match uppercase city initials.

Constraints

1 <= number of rows in Cities <= 200000
(state, city) is unique
state and city are non-empty strings
State-level city list must be alphabetically ordered by city name
matching_letter_count compares first letters case-insensitively
Only include states with city_count >= 3 and matching_letter_count > 0
Final ordering is matching_letter_count DESC, state ASC
SQL submissions run on SQLite 3.27.2
Pandas submissions must return a DataFrame with columns [state, cities, matching_letter_count]

Cities: | state | city | |----------|------------| | Asteria | Afton | | Asteria | Arbor | | Asteria | Apex | | Asteria | Bayview | | Borealis | Brookline | | Borealis | Bridgeport | | Borealis | Belltown | | Borealis | Cedarfield | | Cascade | Dover | | Cascade | Elmwood | | Cascade | Fairview |

Cities: | state | city | |----------|------------| | Elmstate | Eden | | Elmstate | Eastgate | | Elmstate | Everly | | Elmstate | Harborview | | Fjord | Falcon | | Fjord | Flint | | Fjord | Forest | | Fjord | Granite | | Galena | Grove | | Galena | Garden | | Galena | Hillside |

[ {"state":"Elmstate","cities":"Eastgate, Eden, Everly, Harborview","matching_letter_count":3}, {"state":"Fjord","cities":"Falcon, Flint, Forest, Granite","matching_letter_count":3}, {"state":"Galena","cities":"Garden, Grove, Hillside","matching_letter_count":2} ]

Cities: | state | city | |---------|-----------| | nova | Nexus | | nova | Nadir | | nova | Northbay | | nova | Aurora | | Orchid | Opal | | Orchid | Olive | | Orchid | Maple | | Prairie | Pine | | Prairie | Quartz |