Solved/3198. Find Cities in Each State (Easy)-(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": [
    "**3198. Find Cities in Each State (Easy)**\n",
    "\n",
    "**Table: cities**\n",
    "\n",
    "| Column Name | Type    | \n",
    "|-------------|---------|\n",
    "| state       | varchar |\n",
    "| city        | varchar |\n",
    "\n",
    "(state, city) is the primary key (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 combine them into a single comma-separated string.**\n",
    "\n",
    "Return the result table ordered by state and city 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",
    "| California  | Los Angeles   |\n",
    "| California  | San Francisco |\n",
    "| California  | San Diego     |\n",
    "| Texas       | Houston       |\n",
    "| Texas       | Austin        |\n",
    "| Texas       | Dallas        |\n",
    "| New York    | New York City |\n",
    "| New York    | Buffalo       |\n",
    "| New York    | Rochester     |\n",
    "\n",
    "**Output:**\n",
    "\n",
    "| state       | cities                                |\n",
    "|-------------|---------------------------------------|\n",
    "| California  | Los Angeles, San Diego, San Francisco |\n",
    "| New York    | Buffalo, New York City, Rochester     |\n",
    "| Texas       | Austin, Dallas, Houston               |\n",
    "\n",
    "**Explanation:**\n",
    "- **California:** All cities (\"Los Angeles\", \"San Diego\", \"San Francisco\") are listed in a comma-separated string.\n",
    "- **New York:** All cities (\"Buffalo\", \"New York City\", \"Rochester\") are listed in a comma-separated string.\n",
    "- **Texas:** All cities (\"Austin\", \"Dallas\", \"Houston\") are listed in a comma-separated string.\n",
    "\n",
    "**Note:** The output table is ordered by the state name in ascending order."
   ]
  },
  {
   "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_3198 = [\n",
    "    (\"California\", \"Los Angeles\"),\n",
    "    (\"California\", \"San Francisco\"),\n",
    "    (\"California\", \"San Diego\"),\n",
    "    (\"Texas\", \"Houston\"),\n",
    "    (\"Texas\", \"Austin\"),\n",
    "    (\"Texas\", \"Dallas\"),\n",
    "    (\"New York\", \"New York City\"),\n",
    "    (\"New York\", \"Buffalo\"),\n",
    "    (\"New York\", \"Rochester\")\n",
    "]\n",
    "\n",
    "cities_columns_3198 = [\"state\", \"city\"]\n",
    "cities_df_3198 = spark.createDataFrame(cities_data_3198, cities_columns_3198)\n",
    "cities_df_3198.show()"
   ]
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "computePreferences": null,
   "dashboards": [],
   "environmentMetadata": {
    "base_environment": "",
    "environment_version": "1"
   },
   "inputWidgetPreferences": null,
   "language": "python",
   "notebookMetadata": {
    "pythonIndentUnit": 4
   },
   "notebookName": "3198. Find Cities in Each State (Easy)",
   "widgets": {}
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}