Welcome to Subscribe On Youtube

3198. Find Cities in Each State 🔒


Table: cities

\| state       \| varchar \|
\| city        \| varchar \|
\| state       \| city          \|


\| California  \| Los Angeles, San Diego, San Francisco \|
\| New York    \| Buffalo, New York City, Rochester     \|
\| Texas       \| Austin, Dallas, Houston               \|


  • California: All cities ("Los Angeles", "San Diego", "San Francisco") are listed in a comma-separated string.
  • New York: All cities ("Buffalo", "New York City", "Rochester") are listed in a comma-separated string.
  • Texas: All cities ("Austin", "Dallas", "Houston") are listed in a comma-separated string.

Note: The output table is ordered by the state name in ascending order.



Solution 1: Grouping and Aggregation

We can first group by the state field, then sort the city field within each group, and finally use the GROUP_CONCAT function to concatenate the sorted city names into a comma-separated string.

  • import pandas as pd
    def find_cities(cities: pd.DataFrame) -> pd.DataFrame:
        result = (
            .apply(lambda x: ", ".join(sorted(x)))
        result.columns = ["state", "cities"]
        return result
  • # Write your MySQL query statement below
        GROUP_CONCAT(city ORDER BY city SEPARATOR ', ') cities
    FROM cities
    GROUP BY 1
    ORDER BY 1;

All Problems

All Solutions