Solved/1875. Group Employees of the Same Salary (Medium)-(Solved).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "fadf709d-de3d-4bfc-b69f-3b489de68bef",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "source": [
    "## Importing Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "d4a1fef1-aa99-469c-b35f-9b3f3e81c9f7",
     "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": "78daa073-80d8-4f7e-9cfa-10f6eb568909",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "source": [
    "**1875. Group Employees of the Same Salary (Medium)**\n",
    "\n",
    "**Table: Employees**\n",
    "\n",
    "| Column Name | Type    |\n",
    "|-------------|---------|\n",
    "| employee_id | int     |\n",
    "| name        | varchar |\n",
    "| salary      | int     |\n",
    "\n",
    "employee_id is the column with unique values for this table.\n",
    "Each row of this table indicates the employee ID, employee name, and salary.\n",
    " \n",
    "A company wants to divide the employees into teams such that all the members on each team have the same salary. The teams should follow these criteria:\n",
    "- Each team should consist of at least two employees.\n",
    "- All the employees on a team should have the same salary.\n",
    "- All the employees of the same salary should be assigned to the same team.\n",
    "- If the salary of an employee is unique, we do not assign this employee to any team.\n",
    "- A team's ID is assigned based on the rank of the team's salary relative to the other teams' salaries, where the team with the lowest salary has team_id = 1. Note that the salaries for employees not on a team are not included in this ranking.\n",
    "\n",
    "**Write a solution to get the team_id of each employee that is in a team.**\n",
    "\n",
    "Return the result table ordered by team_id in ascending order. In case of a tie, order it by employee_id in ascending order.\n",
    "\n",
    "The result format is in the following example.\n",
    "\n",
    "**Example 1:**\n",
    "\n",
    "**Input:** \n",
    "\n",
    "**Employees table:**\n",
    "\n",
    "| employee_id | name    | salary |\n",
    "|-------------|---------|--------|\n",
    "| 2           | Meir    | 3000   |\n",
    "| 3           | Michael | 3000   |\n",
    "| 7           | Addilyn | 7400   |\n",
    "| 8           | Juan    | 6100   |\n",
    "| 9           | Kannon  | 7400   |\n",
    "\n",
    "**Output:** \n",
    "| employee_id | name    | salary | team_id |\n",
    "|-------------|---------|--------|---------|\n",
    "| 2           | Meir    | 3000   | 1       |\n",
    "| 3           | Michael | 3000   | 1       |\n",
    "| 7           | Addilyn | 7400   | 2       |\n",
    "| 9           | Kannon  | 7400   | 2       |\n",
    "\n",
    "**Explanation:** \n",
    "- Meir (employee_id=2) and Michael (employee_id=3) are in the same team because they have the same salary of 3000.\n",
    "- Addilyn (employee_id=7) and Kannon (employee_id=9) are in the same team because they have the same salary of 7400.\n",
    "- Juan (employee_id=8) is not included in any team because their salary of 6100 is unique (i.e. no other employee has the same salary).\n",
    "- The team IDs are assigned as follows (based on salary ranking, lowest first):\n",
    "  - team_id=1: Meir and Michael, a salary of 3000\n",
    "  - team_id=2: Addilyn and Kannon, a salary of 7400\n",
    "- Juan's salary of 6100 is not included in the ranking because they are not on a team."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "7b78bc27-26cd-4523-8646-7369baf68fcb",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "employees_data_1875 = [\n",
    "    (2, \"Meir\", 3000),\n",
    "    (3, \"Michael\", 3000),\n",
    "    (7, \"Addilyn\", 7400),\n",
    "    (8, \"Juan\", 6100),\n",
    "    (9, \"Kannon\", 7400),\n",
    "]\n",
    "\n",
    "employees_columns_1875 = [\"employee_id\", \"name\", \"salary\"]\n",
    "employees_df_1875 = spark.createDataFrame(employees_data_1875, employees_columns_1875)\n",
    "employees_df_1875.show()\n"
   ]
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "computePreferences": null,
   "dashboards": [],
   "environmentMetadata": {
    "base_environment": "",
    "environment_version": "1"
   },
   "inputWidgetPreferences": null,
   "language": "python",
   "notebookMetadata": {
    "pythonIndentUnit": 4
   },
   "notebookName": "1875. Group Employees of the Same Salary (Medium)",
   "widgets": {}
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}