Solved/1212. Team Scores in Football Tournament (Medium)-(Solved).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "82383caa-9b15-409a-9acb-968b851c477b",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "source": [
    "## Importing Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "83322872-9913-4063-98cf-4aaea5fd3337",
     "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": "e475f002-2b95-4d27-958c-aeab86c066ed",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "source": [
    "**1212. Team Scores in Football Tournament (Medium)**\n",
    "\n",
    "**Table: Teams**\n",
    "\n",
    "| Column Name   | Type     |\n",
    "|---------------|----------|\n",
    "| team_id       | int      |\n",
    "| team_name     | varchar  |\n",
    "\n",
    "team_id is the column with unique values of this table.\n",
    "Each row of this table represents a single football team.\n",
    " \n",
    "**Table: Matches**\n",
    "\n",
    "| Column Name   | Type    |\n",
    "|---------------|---------|\n",
    "| match_id      | int     |\n",
    "| host_team     | int     |\n",
    "| guest_team    | int     | \n",
    "| host_goals    | int     |\n",
    "| guest_goals   | int     |\n",
    "\n",
    "match_id is the column of unique values of this table.\n",
    "Each row is a record of a finished match between two different teams. \n",
    "Teams host_team and guest_team are represented by their IDs in the Teams table (team_id), and they scored host_goals and guest_goals goals, respectively.\n",
    " \n",
    "You would like to compute the scores of all teams after all matches. Points are awarded as follows:\n",
    "- A team receives three points if they win a match (i.e., Scored more goals than the opponent team).\n",
    "- A team receives one point if they draw a match (i.e., Scored the same number of goals as the opponent team).\n",
    "- A team receives no points if they lose a match (i.e., Scored fewer goals than the opponent team).\n",
    "\n",
    "**Write a solution that selects the team_id, team_name and num_points of each team in the tournament after all described matches.**\n",
    "\n",
    "Return the result table ordered by num_points in decreasing order. In case of a tie, order the records by team_id in increasing order.\n",
    "\n",
    "The result format is in the following example.\n",
    "\n",
    "**Example 1:**\n",
    "\n",
    "**Input:** \n",
    "\n",
    "**Teams table:**\n",
    "\n",
    "| team_id   | team_name    |\n",
    "|-----------|--------------|\n",
    "| 10        | Leetcode FC  |\n",
    "| 20        | NewYork FC   |\n",
    "| 30        | Atlanta FC   |\n",
    "| 40        | Chicago FC   |\n",
    "| 50        | Toronto FC   |\n",
    "\n",
    "**Matches table:**\n",
    "| match_id   | host_team    | guest_team    | host_goals  | guest_goals  |\n",
    "|------------|--------------|---------------|-------------|--------------|\n",
    "| 1          | 10           | 20            | 3           | 0            |\n",
    "| 2          | 30           | 10            | 2           | 2            |\n",
    "| 3          | 10           | 50            | 5           | 1            |\n",
    "| 4          | 20           | 30            | 1           | 0            |\n",
    "| 5          | 50           | 30            | 1           | 0            |\n",
    "\n",
    "**Output:** \n",
    "| team_id    | team_name    | num_points    |\n",
    "|------------|--------------|---------------|\n",
    "| 10         | Leetcode FC  | 7             |\n",
    "| 20         | NewYork FC   | 3             |\n",
    "| 50         | Toronto FC   | 3             |\n",
    "| 30         | Atlanta FC   | 1             |\n",
    "| 40         | Chicago FC   | 0             |\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "a073d765-b3a4-4bda-a02f-ac5e3671a222",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "teams_data_1212 = [\n",
    "    (10, \"Leetcode FC\"),\n",
    "    (20, \"NewYork FC\"),\n",
    "    (30, \"Atlanta FC\"),\n",
    "    (40, \"Chicago FC\"),\n",
    "    (50, \"Toronto FC\"),\n",
    "]\n",
    "\n",
    "teams_columns_1212 = [\"team_id\", \"team_name\"]\n",
    "teams_df_1212 = spark.createDataFrame(teams_data_1212, teams_columns_1212)\n",
    "teams_df_1212.show()\n",
    "\n",
    "matches_data_1212 = [\n",
    "    (1, 10, 20, 3, 0),\n",
    "    (2, 30, 10, 2, 2),\n",
    "    (3, 10, 50, 5, 1),\n",
    "    (4, 20, 30, 1, 0),\n",
    "    (5, 50, 30, 1, 0),\n",
    "]\n",
    "\n",
    "matches_columns_1212 = [\"match_id\", \"host_team\", \"guest_team\", \"host_goals\", \"guest_goals\"]\n",
    "matches_df_1212 = spark.createDataFrame(matches_data_1212, matches_columns_1212)\n",
    "matches_df_1212.show()"
   ]
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "computePreferences": null,
   "dashboards": [],
   "environmentMetadata": {
    "base_environment": "",
    "environment_version": "1"
   },
   "inputWidgetPreferences": null,
   "language": "python",
   "notebookMetadata": {
    "pythonIndentUnit": 4
   },
   "notebookName": "1212. Team Scores in Football Tournament (Medium)",
   "widgets": {}
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}