Find Cities in Each State II - Solution & Explanation

+-------------+---------+
| Column Name | Type    | 
+-------------+---------+
| state       | varchar |
| city        | varchar |
+-------------+---------+
(state, city) is the combination of columns with unique values for this table.
Each row of this table contains the state name and the city name within that state.

Input:

cities table:

+--------------+---------------+
| state        | city          |
+--------------+---------------+
| New York     | New York City |
| New York     | Newark        |
| New York     | Buffalo       |
| New York     | Rochester     |
| California   | San Francisco |
| California   | Sacramento    |
| California   | San Diego     |
| California   | Los Angeles   |
| Texas        | Tyler         |
| Texas        | Temple        |
| Texas        | Taylor        |
| Texas        | Dallas        |
| Pennsylvania | Philadelphia  |
| Pennsylvania | Pittsburgh    |
| Pennsylvania | Pottstown     |
+--------------+---------------+

Output:

+-------------+-------------------------------------------+-----------------------+
| state       | cities                                    | matching_letter_count |
+-------------+-------------------------------------------+-----------------------+
| 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