Welcome to Subscribe On Youtube

3328. Find Cities in Each State II 🔒

Description

Table: cities

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

Output:

+-+-+--+
\| Pennsylvania\| Philadelphia, Pittsburgh, Pottstown       \| 3                     \|
\| Texas       \| Dallas, Taylor, Temple, Tyler             \| 3                     \|
\| New York    \| Buffalo, Newark, New York City, Rochester \| 2                     \|
+-+-+-----+

Explanation:

  • Pennsylvania:
    • Has 3 cities (meets minimum requirement)
    • All 3 cities start with 'P' (same as state)
    • matching_letter_count = 3
  • Texas:
    • Has 4 cities (meets minimum requirement)
    • 3 cities (Taylor, Temple, Tyler) start with 'T' (same as state)
    • matching_letter_count = 3
  • New York:
    • Has 4 cities (meets minimum requirement)
    • 2 cities (Newark, New York City) start with 'N' (same as state)
    • matching_letter_count = 2
  • California is not included in the output because:
    • Although it has 4 cities (meets minimum requirement)
    • No cities start with 'C' (doesn't meet the matching letter requirement)

Note:

  • Results are ordered by matching_letter_count in descending order
  • When matching_letter_count is the same (Texas and New York both have 2), they are ordered by state name alphabetically
  • Cities in each row are ordered alphabetically

</div>

Solutions

Solution 1: Group Aggregation + Filtering

We can group the cities table by the state field, then apply filtering on each group to retain only the groups that meet the specified conditions.

  • import pandas as pd


def state_city_analysis(cities: pd.DataFrame) -> pd.DataFrame:
    cities["matching_letter"] = cities["city"].str[0] == cities["state"].str[0]

    result = (
        cities.groupby("state")
        .agg(
            cities=("city", lambda x: ", ".join(sorted(x))),
            matching_letter_count=("matching_letter", "sum"),
            city_count=("city", "count"),
        )
        .reset_index()
    )

    result = result[(result["city_count"] >= 3) & (result["matching_letter_count"] > 0)]

    result = result.sort_values(
        by=["matching_letter_count", "state"], ascending=[False, True]
    )

    result = result.drop(columns=["city_count"])

    return result


  • # Write your MySQL query statement below
SELECT
    state,
    GROUP_CONCAT(city ORDER BY city SEPARATOR ', ') AS cities,
    COUNT(
        CASE
            WHEN LEFT(city, 1) = LEFT(state, 1) THEN 1
        END
    ) AS matching_letter_count
FROM cities
GROUP BY 1
HAVING COUNT(city) >= 3 AND matching_letter_count > 0
ORDER BY 3 DESC, 1;

All Problems

All Solutions