Solved/3328. Find Cities in Each State II (Medium)-(Solved).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "7d1791bd-9552-4116-90b2-daa678bbffb8",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "source": [
    "## Importing Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "2901fbc9-49db-4285-8b94-c460f34d4c4f",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import *\n",
    "from pyspark.sql.types import *\n",
    "from pyspark.sql.window import Window"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "655120e7-6b25-417c-a08a-c6925feaa425",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "source": [
    "**3328. Find Cities in Each State II (Medium)**\n",
    "\n",
    "**Table: cities**\n",
    "\n",
    "| Column Name | Type    | \n",
    "|-------------|---------|\n",
    "| state       | varchar |\n",
    "| city        | varchar |\n",
    "\n",
    "(state, city) is the combination of columns with unique values for this table.\n",
    "Each row of this table contains the state name and the city name within that state.\n",
    "\n",
    "**Write a solution to find all the cities in each state and analyze them based on the following requirements:**\n",
    "- Combine all cities into a comma-separated string for each state.\n",
    "- Only include states that have at least 3 cities.\n",
    "- Only include states where at least one city starts with the same letter as the state name.\n",
    "\n",
    "Return the result table ordered by the count of matching-letter cities in descending order and then by state name in ascending order.\n",
    "\n",
    "The result format is in the following example.\n",
    "\n",
    "**Example:**\n",
    "\n",
    "**Input:**\n",
    "\n",
    "**cities table:**\n",
    "\n",
    "| state        | city          |\n",
    "|--------------|---------------|\n",
    "| New York     | New York City |\n",
    "| New York     | Newark        |\n",
    "| New York     | Buffalo       |\n",
    "| New York     | Rochester     |\n",
    "| California   | San Francisco |\n",
    "| California   | Sacramento    |\n",
    "| California   | San Diego     |\n",
    "| California   | Los Angeles   |\n",
    "| Texas        | Tyler         |\n",
    "| Texas        | Temple        |\n",
    "| Texas        | Taylor        |\n",
    "| Texas        | Dallas        |\n",
    "| Pennsylvania | Philadelphia  |\n",
    "| Pennsylvania | Pittsburgh    |\n",
    "| Pennsylvania | Pottstown     |\n",
    "\n",
    "**Output:**\n",
    "\n",
    "| state       | cities                                    | matching_letter_count |\n",
    "|-------------|-------------------------------------------|-----------------------|\n",
    "| Pennsylvania| Philadelphia, Pittsburgh, Pottstown       | 3                     |\n",
    "| Texas       | Dallas, Taylor, Temple, Tyler             | 3                     |\n",
    "| New York    | Buffalo, Newark, New York City, Rochester | 2                     |\n",
    "\n",
    "\n",
    "**Explanation:**\n",
    "- **Pennsylvania:**\n",
    "  - Has 3 cities (meets minimum requirement)\n",
    "  - All 3 cities start with 'P' (same as state)\n",
    "  - matching_letter_count = 3\n",
    "- **Texas:**\n",
    "  - Has 4 cities (meets minimum requirement)\n",
    "  - 3 cities (Taylor, Temple, Tyler) start with 'T' (same as state)\n",
    "  - matching_letter_count = 3\n",
    "- **New York:**\n",
    "  - Has 4 cities (meets minimum requirement)\n",
    "  - 2 cities (Newark, New York City) start with 'N' (same as state)\n",
    "  - matching_letter_count = 2\n",
    "- **California** is not included in the output because:\n",
    "  - Although it has 4 cities (meets minimum requirement)\n",
    "  - No cities start with 'C' (doesn't meet the matching letter requirement)\n",
    "\n",
    "**Note:**\n",
    "- Results are ordered by matching_letter_count in descending order\n",
    "- When matching_letter_count is the same (Texas and New York both have 2), they are ordered by state name alphabetically\n",
    "- Cities in each row are ordered alphabetically"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "a2368434-0191-416c-aa1d-12cd44cf48e6",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "cities_data_3328 = [\n",
    "    (\"New York\", \"New York City\"),\n",
    "    (\"New York\", \"Newark\"),\n",
    "    (\"New York\", \"Buffalo\"),\n",
    "    (\"New York\", \"Rochester\"),\n",
    "    (\"California\", \"San Francisco\"),\n",
    "    (\"California\", \"Sacramento\"),\n",
    "    (\"California\", \"San Diego\"),\n",
    "    (\"California\", \"Los Angeles\"),\n",
    "    (\"Texas\", \"Tyler\"),\n",
    "    (\"Texas\", \"Temple\"),\n",
    "    (\"Texas\", \"Taylor\"),\n",
    "    (\"Texas\", \"Dallas\"),\n",
    "    (\"Pennsylvania\", \"Philadelphia\"),\n",
    "    (\"Pennsylvania\", \"Pittsburgh\"),\n",
    "    (\"Pennsylvania\", \"Pottstown\"),\n",
    "]\n",
    "\n",
    "cities_columns_3328 = [\"state\", \"city\"]\n",
    "cities_df_3328 = spark.createDataFrame(cities_data_3328, cities_columns_3328)\n",
    "cities_df_3328.show()"
   ]
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "computePreferences": null,
   "dashboards": [],
   "environmentMetadata": {
    "base_environment": "",
    "environment_version": "1"
   },
   "inputWidgetPreferences": null,
   "language": "python",
   "notebookMetadata": {
    "pythonIndentUnit": 4
   },
   "notebookName": "3328. Find Cities in Each State II (Medium)",
   "widgets": {}
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}