Find Cities in Each State II - Practice Coding Problems

Find Cities in Each State II - Problem

Database Medium

Find Cities in Each State II

You're working as a data analyst for a travel company that needs to analyze city data across different states. Given a database table of cities and their corresponding states, your task is to create a comprehensive report with specific filtering criteria.

The Challenge:
• Group all cities by state into comma-separated lists
• Filter states that have at least 3 cities
• Only include states where at least one city starts with the same letter as the state name
• Sort results by the count of "matching-letter" cities (descending), then by state name (ascending)

Input: A cities table with columns state and city
Output: A result table with state, cities (comma-separated), and matching_letter_count

This problem tests your SQL skills in grouping, filtering, string manipulation, and complex sorting criteria.

Input & Output

example_1.sql — Basic Example

💡 Note: Pennsylvania has all 3 cities starting with 'P' (matching state). Texas has 3 cities (Taylor, Temple, Tyler) starting with 'T'. New York has 2 cities (Newark, New York City) starting with 'N'. California is excluded because no cities start with 'C'.

example_2.sql — Edge Case - Minimum Requirements

💡 Note: Florida has exactly 3 cities and all start with 'F'. Ohio has 3 cities but none start with 'O', so it's excluded despite meeting the count requirement.

example_3.sql — Multiple States Same Count

💡 Note: When matching_letter_count is different (Nevada=3, Maine=2), Nevada comes first. Both states meet minimum requirements but Nevada has more matching cities.

Constraints

1 ≤ number of rows ≤ 1000
1 ≤ state.length, city.length ≤ 100
state and city consist of English letters and spaces only
Each (state, city) combination is unique
At least one state will have ≥ 3 cities with ≥ 1 matching city

Visualization

Tap to expand

Understanding the Visualization

Scan Table

Database scans the cities table row by row

Group Formation

GROUP BY creates buckets for each unique state

Aggregation

For each group, compute city list and matching count

Filter Groups

HAVING clause eliminates groups not meeting criteria

Sort Results

ORDER BY sorts remaining groups by count and name

Key Takeaway

🎯 Key Insight: The database engine optimizes this complex query by processing groups efficiently, using indexes where available, and performing filtering and sorting in the most efficient order possible.

Asked in

A Amazon 45 G Google 35 ⊞ Microsoft 28 f Meta 22 Apple 18

The optimal solution uses a single SQL query with GROUP BY to group cities by state, GROUP_CONCAT() to combine cities, SUM(CASE WHEN...) to count matching letters, HAVING clauses for filtering, and ORDER BY for complex sorting. This approach achieves O(n log n) time complexity with excellent database optimization.

Common Approaches

Approach	Time	Space	Notes
✓ Multiple Query Approach	O(n²)	O(n)	Use separate queries for each filtering step and manual result combination
Single SQL Query (Optimal)	O(n log n)	O(n)	Use a single SQL query with GROUP BY, aggregation, HAVING, and ORDER BY

Multiple Query Approach — Algorithm Steps

Query 1: Get all states and count their cities
Query 2: Filter states with at least 3 cities
Query 3: For each qualifying state, check cities with matching first letters
Query 4: Concatenate cities and count matches
Query 5: Sort results by matching count and state name

Visualization

Tap to expand

Step-by-Step Walkthrough

Group by State

First query groups all cities by their state

Count Filter

Second query filters states with at least 3 cities

Letter Matching

Third query checks for matching first letters

Sort Results

Final query sorts by matching count and state name

Code -

solution.c — C

// Simulating multiple SQL queries approach
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <ctype.h>

#define MAX_STATES 50
#define MAX_CITIES 100
#define MAX_NAME_LEN 100

typedef struct {
    char state[MAX_NAME_LEN];
    char cities[1000];
    int matching_letter_count;
} Result;

typedef struct {
    char name[MAX_NAME_LEN];
    char cities[MAX_CITIES][MAX_NAME_LEN];
    int city_count;
} StateData;

int compareResults(const void* a, const void* b) {
    Result* ra = (Result*)a;
    Result* rb = (Result*)b;
    
    if (ra->matching_letter_count != rb->matching_letter_count) {
        return rb->matching_letter_count - ra->matching_letter_count;
    }
    return strcmp(ra->state, rb->state);
}

int findCitiesBruteForce(char cities[][2][MAX_NAME_LEN], int dataSize, Result* results) {
    StateData states[MAX_STATES];
    int stateCount = 0;
    
    // Step 1: Group cities by state
    for (int i = 0; i < dataSize; i++) {
        int found = -1;
        for (int j = 0; j < stateCount; j++) {
            if (strcmp(states[j].name, cities[i][0]) == 0) {
                found = j;
                break;
            }
        }
        
        if (found == -1) {
            strcpy(states[stateCount].name, cities[i][0]);
            strcpy(states[stateCount].cities[0], cities[i][1]);
            states[stateCount].city_count = 1;
            stateCount++;
        } else {
            strcpy(states[found].cities[states[found].city_count], cities[i][1]);
            states[found].city_count++;
        }
    }
    
    int resultCount = 0;
    
    // Step 2 & 3: Filter and process
    for (int i = 0; i < stateCount; i++) {
        if (states[i].city_count >= 3) {
            int matchingCount = 0;
            char stateFirstLetter = toupper(states[i].name[0]);
            
            for (int j = 0; j < states[i].city_count; j++) {
                if (toupper(states[i].cities[j][0]) == stateFirstLetter) {
                    matchingCount++;
                }
            }
            
            if (matchingCount > 0) {
                strcpy(results[resultCount].state, states[i].name);
                results[resultCount].matching_letter_count = matchingCount;
                
                // Sort cities and concatenate
                strcpy(results[resultCount].cities, "");
                for (int j = 0; j < states[i].city_count; j++) {
                    if (j > 0) strcat(results[resultCount].cities, ", ");
                    strcat(results[resultCount].cities, states[i].cities[j]);
                }
                
                resultCount++;
            }
        }
    }
    
    // Step 4: Sort results
    qsort(results, resultCount, sizeof(Result), compareResults);
    
    return resultCount;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

Multiple passes over the data with nested operations

⚠ Quadratic Growth

Space Complexity

O(n)

Need to store intermediate results between queries

⚡ Linearithmic Space

67.0K Views

Medium-High Frequency

~25 min Avg. Time

1.9K 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

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Multiple Query Approach — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler