Find Cities in Each State - Practice Coding Problems

Find Cities in Each State - Problem

Database Easy

You're working on a geographic data processing system that needs to organize city information by state. Given a database table containing city and state information, your task is to group all cities within each state into a single, comma-separated string.

The cities table contains:

state: The name of the state (varchar)
city: The name of the city within that state (varchar)

Your goal is to:

Group cities by state
Combine city names into comma-separated strings
Sort cities alphabetically within each state
Sort states alphabetically in the result

This is a classic SQL aggregation problem that tests your understanding of GROUP BY, string aggregation functions, and ORDER BY clauses.

Input & Output

example_1.sql — Basic Grouping

💡 Note: Cities are grouped by state and concatenated alphabetically. California has 3 cities, New York has 1, and Texas has 2. States are ordered alphabetically in the result.

example_2.sql — Single City States

💡 Note: When a state has only one city, that city appears alone in the result. States are still ordered alphabetically.

example_3.sql — Large Dataset

💡 Note: Demonstrates handling of states with many cities. Florida has 4 cities and Ohio has 3, all properly sorted alphabetically within each state.

Constraints

1 ≤ number of rows ≤ 10⁴
1 ≤ state.length, city.length ≤ 50
state and city contain only English letters and spaces
No duplicate (state, city) pairs in the input
At least one row will be present in the table

Visualization

Tap to expand

Understanding the Visualization

Scatter the Cards

Individual city cards are scattered - each has a state and city name

Group by State

GROUP BY creates separate piles for each state

Sort Within Groups

Cities within each state pile are sorted alphabetically

Create Brochures

String aggregation combines cities into comma-separated lists

Final Organization

State brochures are arranged alphabetically

Key Takeaway

🎯 Key Insight: SQL's GROUP BY with string aggregation functions (GROUP_CONCAT, STRING_AGG, LISTAGG) provides an optimal O(n log n) solution that groups, sorts, and concatenates data efficiently in a single query operation.

Asked in

a Amazon 45 G Google 38 ⊞ Microsoft 32 f Meta 28

The optimal solution uses GROUP BY with string aggregation functions to efficiently group cities by state and combine them into comma-separated strings. Use GROUP_CONCAT() in MySQL, STRING_AGG() in PostgreSQL, or LISTAGG() in Oracle. The key is to sort cities within each group and sort the final results by state for the required output format.

Common Approaches

Approach	Time	Space	Notes
✓ SQL GROUP BY with String Aggregation (Optimal)	O(n log n)	O(n)	Use GROUP BY with built-in string aggregation functions for optimal performance
Nested Queries Approach	O(n²)	O(n)	Use subqueries to find states and their cities separately

SQL GROUP BY with String Aggregation (Optimal) — Algorithm Steps

Use GROUP BY to group rows by state
Apply string aggregation function to combine cities
Use ORDER BY within aggregation to sort cities alphabetically
Sort final results by state name

Visualization

Tap to expand

Step-by-Step Walkthrough

Group Formation

GROUP BY creates buckets for each state

City Collection

Cities are collected and sorted within each group

String Aggregation

Aggregation function combines cities into comma-separated strings

Final Sort

Results are sorted by state name

Code -

solution.c — C

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <mysql/mysql.h>

typedef struct {
    char state[256];
    char cities[2048];
} Result;

int findCitiesOptimal(MYSQL* conn, Result* results) {
    const char* query = 
        "SELECT "
        "    state, "
        "    GROUP_CONCAT(city ORDER BY city SEPARATOR ', ') AS cities "
        "FROM cities "
        "GROUP BY state "
        "ORDER BY state";
    
    if (mysql_query(conn, query)) {
        fprintf(stderr, "Query failed: %s\n", mysql_error(conn));
        return -1;
    }
    
    MYSQL_RES* result = mysql_store_result(conn);
    if (result == NULL) {
        fprintf(stderr, "mysql_store_result() failed\n");
        return -1;
    }
    
    int num_rows = 0;
    MYSQL_ROW row;
    
    while ((row = mysql_fetch_row(result)) != NULL) {
        strcpy(results[num_rows].state, row[0]);
        strcpy(results[num_rows].cities, row[1]);
        num_rows++;
    }
    
    mysql_free_result(result);
    return num_rows;
}

// Alternative query for PostgreSQL
const char* getPostgreSQLQuery() {
    return 
        "SELECT "
        "    state, "
        "    STRING_AGG(city, ', ' ORDER BY city) AS cities "
        "FROM cities "
        "GROUP BY state "
        "ORDER BY state";
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single pass for grouping plus sorting within groups and final result sorting

⚡ Linearithmic

Space Complexity

O(n)

Space for grouped results and concatenated strings

⚡ Linearithmic Space

26.4K Views

Medium Frequency

~12 min Avg. Time

892 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

SQL GROUP BY with String Aggregation (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler