Welcome to Subscribe On Youtube

3198. Find Cities in Each State 🔒

Description

Table: cities

+-++
\| state       \| varchar \|
\| city        \| varchar \|
+-++
\| state       \| city          \|
+-++

Output:

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

Explanation:

• 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.

</div>

Solutions

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.

• Python
• SQL

• import pandas as pd
  
  
  def find_cities(cities: pd.DataFrame) -> pd.DataFrame:
      result = (
          cities.groupby("state")["city"]
          .apply(lambda x: ", ".join(sorted(x)))
          .reset_index()
      )
      result.columns = ["state", "cities"]
      return result
  
  

• # Write your MySQL query statement below
  SELECT
      state,
      GROUP_CONCAT(city ORDER BY city SEPARATOR ', ') cities
  FROM cities
  GROUP BY 1
  ORDER BY 1;
  

All Problems

All Solutions