00:00:00

Description

Editorial

State City Roster with Initial-Letter Match

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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]

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Cities =

[{"state":"Asteria","city":"Afton"},{"state":"Asteria","city":"Arbor"},{"state":"Asteria","city":"Apex"},{"state":"Asteria","city":"Bayview"},{"state":"Borealis","city":"Brookline"},{"state":"Borealis","city":"Bridgeport"},{"state":"Borealis","city":"Belltown"},{"state":"Borealis","city":"Cedarfield"},{"state":"Cascade","city":"Dover"},{"state":"Cascade","city":"Elmwood"},{"state":"Cascade","city":"Fairview"}]

Loading problem...

101

00:00:00

Description

Editorial

State City Roster with Initial-Letter Match

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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]

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Cities =

State City Roster with Initial-Letter Match

Hints

State City Roster with Initial-Letter Match

Hints