State City Directory Rollup

Easy

A geography data platform stores city membership records by state and needs a deterministic per-state summary string for downstream reporting. Table: Cities state: state name city: city name The pair (state, city) is unique. Task: For every state, produce one row that contains: state cities: a comma-plus-space separated list of all city names from that state, sorted in ascending lexicographic order. Output requirements: Return exactly these columns in this order: state, cities Sort output rows by state in ascending order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Cities:
| state      | city          |
|------------|---------------|
| California | Los Angeles   |
| California | San Francisco |
| California | San Diego     |
| Texas      | Houston       |
| Texas      | Austin        |
| Texas      | Dallas        |
| New York   | New York City |
| New York   | Buffalo       |
| New York   | Rochester     |

Output

[
  {"state":"California","cities":"Los Angeles, San Diego, San Francisco"},
  {"state":"New York","cities":"Buffalo, New York City, Rochester"},
  {"state":"Texas","cities":"Austin, Dallas, Houston"}
]

Explanation

Each state gets one output row. Cities are sorted alphabetically inside each state before joining with ', '.

Example 2

Input

Cities:
| state   | city       |
|---------|------------|
| Nevada  | Reno       |
| Nevada  | Henderson  |
| Nevada  | Las Vegas  |
| Nevada  | Carson City|

Output

[
  {"state":"Nevada","cities":"Carson City, Henderson, Las Vegas, Reno"}
]

Explanation

With a single state, output still requires sorted city concatenation.

Example 3

Input

Cities:
| state     | city        |
|-----------|-------------|
| Oregon    | Portland    |
| Arizona   | Mesa        |
| Oregon    | Salem       |
| Arizona   | Phoenix     |
| Arizona   | Tucson      |
| Oregon    | Eugene      |

Output

[
  {"state":"Arizona","cities":"Mesa, Phoenix, Tucson"},
  {"state":"Oregon","cities":"Eugene, Portland, Salem"}
]

Explanation

Rows are sorted by state, and each state's city list is sorted independently.

Constraints

1 <= number of rows in Cities <= 200000
(state, city) is unique in each test case
state and city are non-empty text values
Return exactly two columns: state, cities
Within each state, city names in cities must be sorted ascending
Joined city separator must be exactly ', ' (comma + space)
Output rows must be sorted by state ascending
SQL submissions are evaluated on SQLite 3.27.2
Pandas submissions must return a DataFrame with columns [state, cities]

Cities: | state | city | |------------|---------------| | California | Los Angeles | | California | San Francisco | | California | San Diego | | Texas | Houston | | Texas | Austin | | Texas | Dallas | | New York | New York City | | New York | Buffalo | | New York | Rochester |

[ {"state":"California","cities":"Los Angeles, San Diego, San Francisco"}, {"state":"New York","cities":"Buffalo, New York City, Rochester"}, {"state":"Texas","cities":"Austin, Dallas, Houston"} ]

Cities: | state | city | |-----------|-------------| | Oregon | Portland | | Arizona | Mesa | | Oregon | Salem | | Arizona | Phoenix | | Arizona | Tucson | | Oregon | Eugene |