Find Cities in Each State II - Practice Coding Problems

Find Cities in Each State II - Problem

Database Medium

You are given a table cities with information about cities in different states.

Write a SQL query to:

Find all cities in each state and combine them into a comma-separated string
Only include states that have at least 3 cities
Only include states where at least one city starts with the same letter as the state name
Return results ordered by the count of matching-letter cities in descending order, then by state name in ascending order
Cities in each row should be ordered alphabetically

Example: For state "Texas" with cities ["Tyler", "Temple", "Taylor", "Dallas"], the matching cities are "Tyler", "Temple", "Taylor" (all start with 'T'), so matching_letter_count = 3.

Table Schema

cities

Column Name	Type	Description
`state` PK	varchar	State name
`city` PK	varchar	City name within the state

Primary Key: (state, city)

Note: Each row represents a unique state-city combination

Input & Output

Example 1 — Multiple States with Different Match Counts

Input Table:

state	city
New York	New York City
New York	Newark
New York	Buffalo
New York	Rochester
California	San Francisco
California	Sacramento
California	San Diego
California	Los Angeles
Texas	Tyler
Texas	Temple
Texas	Taylor
Texas	Dallas
Pennsylvania	Philadelphia
Pennsylvania	Pittsburgh
Pennsylvania	Pottstown

Output:

state	cities	matching_letter_count
Pennsylvania	Philadelphia, Pittsburgh, Pottstown	3
Texas	Dallas, Taylor, Temple, Tyler	3
New York	Buffalo, Newark, New York City, Rochester	2

💡 Note:

Pennsylvania: 3 cities, all start with 'P' → matching_letter_count = 3

Texas: 4 cities, 3 start with 'T' (Taylor, Temple, Tyler) → matching_letter_count = 3

New York: 4 cities, 2 start with 'N' (Newark, New York City) → matching_letter_count = 2

California: Excluded because no cities start with 'C'

Example 2 — State with Minimum Requirements

Input Table:

state	city
Florida	Fort Lauderdale
Florida	Fresno
Florida	Miami
Nevada	Las Vegas
Nevada	Reno

Output:

state	cities	matching_letter_count
Florida	Fort Lauderdale, Fresno, Miami	2

💡 Note:

Florida: Has exactly 3 cities (meets minimum), 2 cities start with 'F' → included with matching_letter_count = 2

Nevada: Only has 2 cities (below minimum of 3) → excluded

Constraints

1 ≤ number of cities ≤ 1000
state and city contain only letters and spaces
Each (state, city) combination is unique

Visualization

Tap to expand

Understanding the Visualization

Input

Cities table with state-city pairs

Group & Filter

GROUP BY state with HAVING conditions

Output

Aggregated cities with match counts

Key Takeaway

🎯 Key Insight: Use GROUP BY with conditional aggregation and HAVING clause to filter grouped results based on computed conditions

Asked in

A Amazon 28 M Microsoft 22 G Google 19

Use GROUP BY with STRING_AGG to combine cities per state, apply HAVING filters for minimum city count and matching letter requirement, then use conditional aggregation to count matching cities.

Table Schema

cities

Column Name	Type	Description
`state` PK	varchar	State name
`city` PK	varchar	City name within the state

Primary Key: (state, city)

Note: Each row represents a unique state-city combination

Common Approaches

Approach	Time	Space	Notes
✓ GROUP BY with String Aggregation	O(n log n)	O(n)	Group cities by state, filter with HAVING, and use conditional aggregation

GROUP BY with String Aggregation — Algorithm Steps

Step 1: GROUP BY state to aggregate cities per state
Step 2: Use STRING_AGG to create comma-separated city lists
Step 3: Apply HAVING filters for minimum 3 cities and matching letter requirement
Step 4: Count cities starting with same letter as state using SUM(CASE WHEN)
Step 5: Order by matching count DESC, then state name ASC

Visualization

Tap to expand

Step-by-Step Walkthrough

Group

Group cities by state

Aggregate

Combine cities and count matches

Filter

Apply HAVING conditions

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Grouping and sorting operations

⚡ Linearithmic

Space Complexity

O(n)

Storage for grouped results and string aggregation

⚡ Linearithmic Space

23.4K Views

Medium Frequency

~18 min Avg. Time

890 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

GROUP BY with String Aggregation — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler