Unsolved/3338. Second Highest Salary II (Medium).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": [
    "**3338. Second Highest Salary II (Medium)**\n",
    "\n",
    "**Table: employees**\n",
    "\n",
    "| Column Name      | Type    |\n",
    "|------------------|---------|\n",
    "| emp_id           | int     |\n",
    "| salary           | int     |\n",
    "| dept             | varchar |\n",
    "\n",
    "emp_id is the unique key for this table.\n",
    "Each row of this table contains information about an employee including their ID, salary, and department.\n",
    "\n",
    "**Write a solution to find the employees who earn the second-highest salary in each department. If multiple employees have the second-highest salary, include all employees with that salary.**\n",
    "\n",
    "Return the result table ordered by emp_id in ascending order.\n",
    "\n",
    "The result format is in the following example.\n",
    "\n",
    "**Example:**\n",
    "\n",
    "**Input:**\n",
    "\n",
    "**employees table:**\n",
    "\n",
    "| emp_id | salary | dept      |\n",
    "|--------|--------|-----------|\n",
    "| 1      | 70000  | Sales     |\n",
    "| 2      | 80000  | Sales     |\n",
    "| 3      | 80000  | Sales     |\n",
    "| 4      | 90000  | Sales     |\n",
    "| 5      | 55000  | IT        |\n",
    "| 6      | 65000  | IT        |\n",
    "| 7      | 65000  | IT        |\n",
    "| 8      | 50000  | Marketing |\n",
    "| 9      | 55000  | Marketing |\n",
    "| 10     | 55000  | HR        |\n",
    "\n",
    "**Output:**\n",
    "\n",
    "| emp_id | dept      |\n",
    "|--------|-----------|\n",
    "| 2      | Sales     |\n",
    "| 3      | Sales     |\n",
    "| 5      | IT        |\n",
    "| 8      | Marketing |\n",
    "\n",
    "**Explanation:**\n",
    "\n",
    "- **Sales Department:**\n",
    "  - Highest salary is 90000 (emp_id: 4)\n",
    "  - Second-highest salary is 80000 (emp_id: 2, 3)\n",
    "  - Both employees with salary 80000 are included\n",
    "- **IT Department:**\n",
    "  - Highest salary is 65000 (emp_id: 6, 7)\n",
    "  - Second-highest salary is 55000 (emp_id: 5)\n",
    "  - Only emp_id 5 is included as they have the second-highest salary\n",
    "- **Marketing Department:**\n",
    "  - Highest salary is 55000 (emp_id: 9)\n",
    "  - Second-highest salary is 50000 (emp_id: 8)\n",
    "  - Employee 8 is included\n",
    "- **HR Department:**\n",
    "  - Only has one employee\n",
    "  - Not included in the result as it has fewer than 2 employees"
   ]
  },
  {
   "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": [
    "employees_data_3338 = [\n",
    "    (1, 70000, \"Sales\"),\n",
    "    (2, 80000, \"Sales\"),\n",
    "    (3, 80000, \"Sales\"),\n",
    "    (4, 90000, \"Sales\"),\n",
    "    (5, 55000, \"IT\"),\n",
    "    (6, 65000, \"IT\"),\n",
    "    (7, 65000, \"IT\"),\n",
    "    (8, 50000, \"Marketing\"),\n",
    "    (9, 55000, \"Marketing\"),\n",
    "    (10, 55000, \"HR\"),\n",
    "]\n",
    "\n",
    "employees_columns_3338 = [\"emp_id\", \"salary\", \"dept\"]\n",
    "employees_df_3338 = spark.createDataFrame(employees_data_3338, employees_columns_3338)\n",
    "employees_df_3338.show()"
   ]
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "computePreferences": null,
   "dashboards": [],
   "environmentMetadata": {
    "base_environment": "",
    "environment_version": "1"
   },
   "inputWidgetPreferences": null,
   "language": "python",
   "notebookMetadata": {
    "pythonIndentUnit": 4
   },
   "notebookName": "3338. Second Highest Salary II (Medium)",
   "widgets": {}
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}