Find Cities in Each State II - Problem

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, New York City, Newark, 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
Find Cities in Each State II INPUT State City Arizona Apache Junction Arizona Avondale Arizona Phoenix Arizona Tucson California Compton California Corona California Los Angeles Texas Houston Criteria: 1. At least 3 cities per state 2. At least 1 city starts with same letter as state name ALGORITHM STEPS 1 Group by State Aggregate cities for each state using GROUP BY 2 Filter: Count >= 3 HAVING COUNT(*) >= 3 keeps valid states 3 Check Letter Match LEFT(city,1) = LEFT(state,1) Count matching cities 4 Order Results Sort by match_count DESC, then state_name ASC Letter Match Example: Arizona --> Apache, Avondale 'A' == 'A' [OK] match_count=2 California --> Compton, Corona FINAL RESULT State Cities Cnt Arizona Apache Junction, Avondale, Phoenix, Tucson 2 California Compton, Corona, Los Angeles 2 Query Complete! States with 3+ cities and matching first letters found Output Format: state_name, cities (comma-sep), matching_letter_count ORDER BY cnt DESC, state Key Insight: Use STRING_AGG or GROUP_CONCAT to combine cities into comma-separated lists. Apply conditional counting with CASE WHEN to count cities starting with state's first letter. HAVING clause filters groups, not individual rows - perfect for "at least N" conditions. TutorialsPoint - Find Cities in Each State II | Optimal Solution

Related Problems

Asked in
Amazon 28 Microsoft 22 Google 19
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