Unsolved/615. Average Salary_ Department Vs Company (Hard).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "4cd0cff4-1b3d-443e-8f60-5eaa3c44b351",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "source": [
    "## Importing Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "97710283-7ef7-4994-bdf7-ab35e4e4acd8",
     "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": "d438d974-0dc5-432f-af61-ceb185a0700c",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "source": [
    "**615. Average Salary: Department Vs Company (Hard)**\n",
    "\n",
    "**Table: Salary**\n",
    "\n",
    "| Column Name | Type |\n",
    "|-------------|------|\n",
    "| id          | int  |\n",
    "| employee_id | int  |\n",
    "| amount      | int  |\n",
    "| pay_date    | date |\n",
    "\n",
    "In SQL, id is the primary key column for this table.\n",
    "Each row of this table indicates the salary of an employee in one month.\n",
    "employee_id is a foreign key (reference column) from the Employee table.\n",
    " \n",
    "**Table: Employee**\n",
    "\n",
    "| Column Name   | Type |\n",
    "|---------------|------|\n",
    "| employee_id   | int  |\n",
    "| department_id | int  |\n",
    "\n",
    "In SQL, employee_id is the primary key column for this table.\n",
    "Each row of this table indicates the department of an employee.\n",
    " \n",
    "**Find the comparison result (higher/lower/same) of the average salary of employees in a department to the company's average salary.**\n",
    "\n",
    "Return the result table in any order.\n",
    "\n",
    "The result format is in the following example.\n",
    "\n",
    "**Example 1:**\n",
    "\n",
    "**Input:** \n",
    "\n",
    "**Salary table:**\n",
    "\n",
    "| id | employee_id | amount | pay_date   |\n",
    "|----|-------------|--------|------------|\n",
    "| 1  | 1           | 9000   | 2017/03/31 |\n",
    "| 2  | 2           | 6000   | 2017/03/31 |\n",
    "| 3  | 3           | 10000  | 2017/03/31 |\n",
    "| 4  | 1           | 7000   | 2017/02/28 |\n",
    "| 5  | 2           | 6000   | 2017/02/28 |\n",
    "| 6  | 3           | 8000   | 2017/02/28 |\n",
    "\n",
    "**Employee table:**\n",
    "| employee_id | department_id |\n",
    "|-------------|---------------|\n",
    "| 1           | 1             |\n",
    "| 2           | 2             |\n",
    "| 3           | 2             |\n",
    "\n",
    "**Output:** \n",
    "| pay_month | department_id | comparison |\n",
    "|-----------|---------------|------------|\n",
    "| 2017-02   | 1             | same       |\n",
    "| 2017-03   | 1             | higher     |\n",
    "| 2017-02   | 2             | same       |\n",
    "| 2017-03   | 2             | lower      |\n",
    "\n",
    "**Explanation:** \n",
    "- In March, the company's average salary is (9000+6000+10000)/3 = 8333.33...\n",
    "- The average salary for department '1' is 9000, which is the salary of employee_id '1' since there is only one employee in this department. So the comparison result is 'higher' since 9000 > 8333.33 obviously.\n",
    "- The average salary of department '2' is (6000 + 10000)/2 = 8000, which is the average of employee_id '2' and '3'. So the comparison result is 'lower' since 8000 < 8333.33.\n",
    "\n",
    "With he same formula for the average salary comparison in February, the result is 'same' since both the department '1' and '2' have the same average salary with the company, which is 7000."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "c899699d-2ed8-4227-ba6f-ca46f3f95a7f",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "salary_data_615 = [\n",
    "    (1, 1, 9000, \"2017-03-31\"),\n",
    "    (2, 2, 6000, \"2017-03-31\"),\n",
    "    (3, 3, 10000, \"2017-03-31\"),\n",
    "    (4, 1, 7000, \"2017-02-28\"),\n",
    "    (5, 2, 6000, \"2017-02-28\"),\n",
    "    (6, 3, 8000, \"2017-02-28\"),\n",
    "]\n",
    "salary_columns_615 = [\"id\", \"employee_id\", \"amount\", \"pay_date\"]\n",
    "salary_df_615 = spark.createDataFrame(salary_data_615, salary_columns_615)\n",
    "salary_df_615.show()\n",
    "\n",
    "employee_data_615 = [\n",
    "    (1, 1),\n",
    "    (2, 2),\n",
    "    (3, 2),\n",
    "]\n",
    "\n",
    "employee_columns_615 = [\"employee_id\", \"department_id\"]\n",
    "employee_df_615 = spark.createDataFrame(employee_data_615, employee_columns_615)\n",
    "employee_df_615.show()"
   ]
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "computePreferences": null,
   "dashboards": [],
   "environmentMetadata": {
    "base_environment": "",
    "environment_version": "1"
   },
   "inputWidgetPreferences": null,
   "language": "python",
   "notebookMetadata": {
    "pythonIndentUnit": 4
   },
   "notebookName": "615. Average Salary: Department Vs Company (Hard)",
   "widgets": {}
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}