PySpark_Practice_Queries.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "e57e7122-c745-4d98-aa93-c21aef1f8e8d",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import *\n",
    "from pyspark.sql.types import * \n",
    "from pyspark.sql.window import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "e3584d13-8c58-4dde-8eb6-b31dc73b084f",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "# Define the data\n",
    "data = [\n",
    " (1, \"John\", 30, \"Sales\", 50000.0),\n",
    " (2, \"Alice\", 28, \"Marketing\", 60000.0),\n",
    " (3, \"Bob\", 32, \"Finance\", 55000.0),\n",
    " (4, \"Sarah\", 29, \"Sales\", 52000.0),\n",
    " (5, \"Mike\", 31, \"Finance\", 58000.0)\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "176f7d5f-40b7-4eb3-8d48-a6786ee293d7",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "\n",
    "# Define the schema\n",
    "schema = StructType([\n",
    " StructField(\"id\", IntegerType(), nullable=False),\n",
    " StructField(\"name\", StringType(), nullable=False),\n",
    " StructField(\"age\", IntegerType(), nullable=False),\n",
    " StructField(\"department\", StringType(), nullable=False),\n",
    " StructField(\"salary\", DoubleType(), nullable=False)\n",
    "])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "6f0d28da-29a2-4b28-b467-36063894e218",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "# Create the DataFrame\n",
    "employeeDF = spark.createDataFrame(data, schema)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "248050f1-d915-406f-b48e-e3f67cf76732",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+-----+---+----------+-------+\n| id| name|age|department| salary|\n+---+-----+---+----------+-------+\n|  1| John| 30|     Sales|50000.0|\n|  2|Alice| 28| Marketing|60000.0|\n|  3|  Bob| 32|   Finance|55000.0|\n|  4|Sarah| 29|     Sales|52000.0|\n|  5| Mike| 31|   Finance|58000.0|\n+---+-----+---+----------+-------+\n\n"
     ]
    }
   ],
   "source": [
    "# Show the DataFrame\n",
    "employeeDF.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "eb824c18-8df8-4f3d-b246-771372ab48e4",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "##1. Calculate the average salary for each department"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "9a9c92c5-0fbe-4acc-be2e-674a6848d884",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>department</th><th>average_salary_by_dept</th></tr></thead><tbody><tr><td>Sales</td><td>51000.0</td></tr><tr><td>Marketing</td><td>60000.0</td></tr><tr><td>Finance</td><td>56500.0</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         "Sales",
         51000.0
        ],
        [
         "Marketing",
         60000.0
        ],
        [
         "Finance",
         56500.0
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "department",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "average_salary_by_dept",
         "type": "\"double\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "#Calculate the average salary for each department\n",
    "avgSalbyDept=employeeDF\\\n",
    "    .groupBy(\"department\")\\\n",
    "    .agg(avg(\"salary\").alias(\"average_salary_by_dept\"))\n",
    "\n",
    "avgSalbyDept.display()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "475dddd6-88b8-4fe8-9b0e-69eee79f1bfb",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "##2. Add a new column named \"bonus\" that is 10% of the salary for all employees."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "e98ece95-6cb5-4b92-87dd-5708000bcdf1",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+-----+---+----------+-------+------+\n| id| name|age|department| salary| bonus|\n+---+-----+---+----------+-------+------+\n|  1| John| 30|     Sales|50000.0|5000.0|\n|  2|Alice| 28| Marketing|60000.0|6000.0|\n|  3|  Bob| 32|   Finance|55000.0|5500.0|\n|  4|Sarah| 29|     Sales|52000.0|5200.0|\n|  5| Mike| 31|   Finance|58000.0|5800.0|\n+---+-----+---+----------+-------+------+\n\n"
     ]
    }
   ],
   "source": [
    "#Add a new column named \"bonus\" that is 10% of the salary for all employees.\n",
    "employeeDF=employeeDF.selectExpr('*','salary * 0.1 as bonus')\n",
    "employeeDF.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "177e5914-7032-4e51-886b-b30046b7e257",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>id</th><th>name</th><th>age</th><th>department</th><th>salary</th><th>bonus</th><th>bonustest</th></tr></thead><tbody><tr><td>1</td><td>John</td><td>30</td><td>Sales</td><td>50000.0</td><td>5000.0</td><td>5000.0</td></tr><tr><td>2</td><td>Alice</td><td>28</td><td>Marketing</td><td>60000.0</td><td>6000.0</td><td>6000.0</td></tr><tr><td>3</td><td>Bob</td><td>32</td><td>Finance</td><td>55000.0</td><td>5500.0</td><td>5500.0</td></tr><tr><td>4</td><td>Sarah</td><td>29</td><td>Sales</td><td>52000.0</td><td>5200.0</td><td>5200.0</td></tr><tr><td>5</td><td>Mike</td><td>31</td><td>Finance</td><td>58000.0</td><td>5800.0</td><td>5800.0</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         1,
         "John",
         30,
         "Sales",
         50000.0,
         5000.0,
         5000.0
        ],
        [
         2,
         "Alice",
         28,
         "Marketing",
         60000.0,
         6000.0,
         6000.0
        ],
        [
         3,
         "Bob",
         32,
         "Finance",
         55000.0,
         5500.0,
         5500.0
        ],
        [
         4,
         "Sarah",
         29,
         "Sales",
         52000.0,
         5200.0,
         5200.0
        ],
        [
         5,
         "Mike",
         31,
         "Finance",
         58000.0,
         5800.0,
         5800.0
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "id",
         "type": "\"integer\""
        },
        {
         "metadata": "{}",
         "name": "name",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "age",
         "type": "\"integer\""
        },
        {
         "metadata": "{}",
         "name": "department",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "salary",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "bonus",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "bonustest",
         "type": "\"double\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "employeeDFbonus = employeeDF.withColumn(\"bonustest\",col(\"salary\")*lit(0.1))\n",
    "employeeDFbonus.display()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "472a65b4-676e-4ffb-8b3f-75f335b3f84f",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "##3. Group the data by department and find the employee with the highest salary in each department"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "5c587d1f-bc24-438e-baf9-d04be8bd57b2",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>id</th><th>name</th><th>age</th><th>department</th><th>salary</th><th>bonus</th><th>row_number</th></tr></thead><tbody><tr><td>5</td><td>Mike</td><td>31</td><td>Finance</td><td>58000.0</td><td>5800.0</td><td>1</td></tr><tr><td>3</td><td>Bob</td><td>32</td><td>Finance</td><td>55000.0</td><td>5500.0</td><td>2</td></tr><tr><td>2</td><td>Alice</td><td>28</td><td>Marketing</td><td>60000.0</td><td>6000.0</td><td>1</td></tr><tr><td>4</td><td>Sarah</td><td>29</td><td>Sales</td><td>52000.0</td><td>5200.0</td><td>1</td></tr><tr><td>1</td><td>John</td><td>30</td><td>Sales</td><td>50000.0</td><td>5000.0</td><td>2</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         5,
         "Mike",
         31,
         "Finance",
         58000.0,
         5800.0,
         1
        ],
        [
         3,
         "Bob",
         32,
         "Finance",
         55000.0,
         5500.0,
         2
        ],
        [
         2,
         "Alice",
         28,
         "Marketing",
         60000.0,
         6000.0,
         1
        ],
        [
         4,
         "Sarah",
         29,
         "Sales",
         52000.0,
         5200.0,
         1
        ],
        [
         1,
         "John",
         30,
         "Sales",
         50000.0,
         5000.0,
         2
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "id",
         "type": "\"integer\""
        },
        {
         "metadata": "{}",
         "name": "name",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "age",
         "type": "\"integer\""
        },
        {
         "metadata": "{}",
         "name": "department",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "salary",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "bonus",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "row_number",
         "type": "\"integer\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "#Group the data by department and find the employee with the highest salary in each department\n",
    "windowSpec =Window.partitionBy(\"department\").orderBy(desc(\"salary\"))\n",
    "highestSalaryByDept=employeeDF.withColumn(\"row_number\",row_number().over(windowSpec))\n",
    "highestSalaryByDept.display()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "21c9212a-259f-4c8c-be4d-782e992e57cf",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>id</th><th>name</th><th>age</th><th>department</th><th>salary</th><th>bonus</th><th>row_number</th></tr></thead><tbody><tr><td>5</td><td>Mike</td><td>31</td><td>Finance</td><td>58000.0</td><td>5800.0</td><td>1</td></tr><tr><td>2</td><td>Alice</td><td>28</td><td>Marketing</td><td>60000.0</td><td>6000.0</td><td>1</td></tr><tr><td>4</td><td>Sarah</td><td>29</td><td>Sales</td><td>52000.0</td><td>5200.0</td><td>1</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         5,
         "Mike",
         31,
         "Finance",
         58000.0,
         5800.0,
         1
        ],
        [
         2,
         "Alice",
         28,
         "Marketing",
         60000.0,
         6000.0,
         1
        ],
        [
         4,
         "Sarah",
         29,
         "Sales",
         52000.0,
         5200.0,
         1
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "id",
         "type": "\"integer\""
        },
        {
         "metadata": "{}",
         "name": "name",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "age",
         "type": "\"integer\""
        },
        {
         "metadata": "{}",
         "name": "department",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "salary",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "bonus",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "row_number",
         "type": "\"integer\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "highestSalaryByDept.filter(col(\"row_number\")==1).display()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "b26717ce-fd22-44d6-9834-cf6107dcce1e",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "##4.  Find the top 3 departments with the highest total salary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "68b5bbe8-95f4-41ca-8591-c145efbb6fc9",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+-----------------+\n|department|totalSalaryByDept|\n+----------+-----------------+\n|   Finance|         113000.0|\n|     Sales|         102000.0|\n| Marketing|          60000.0|\n+----------+-----------------+\n\n"
     ]
    }
   ],
   "source": [
    "#Find the top 3 departments with the highest total salary\n",
    "highestTotalSalaryByDept=employeeDF\\\n",
    "    .groupBy(\"department\")\\\n",
    "    .agg(sum(\"salary\").alias(\"totalSalaryByDept\"))\\\n",
    "    .orderBy(desc(\"totalSalaryByDept\"))\n",
    "\n",
    "highestTotalSalaryByDept.show(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "a631ae70-cb43-41d7-83be-779a03e7713a",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## 5. Find the top most department having highest salary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "40fba151-5bec-4078-a69b-c2ae4442ef0f",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>id</th><th>name</th><th>age</th><th>department</th><th>salary</th><th>bonus</th></tr></thead><tbody><tr><td>1</td><td>John</td><td>30</td><td>Sales</td><td>50000.0</td><td>5000.0</td></tr><tr><td>2</td><td>Alice</td><td>28</td><td>Marketing</td><td>60000.0</td><td>6000.0</td></tr><tr><td>3</td><td>Bob</td><td>32</td><td>Finance</td><td>55000.0</td><td>5500.0</td></tr><tr><td>4</td><td>Sarah</td><td>29</td><td>Sales</td><td>52000.0</td><td>5200.0</td></tr><tr><td>5</td><td>Mike</td><td>31</td><td>Finance</td><td>58000.0</td><td>5800.0</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         1,
         "John",
         30,
         "Sales",
         50000.0,
         5000.0
        ],
        [
         2,
         "Alice",
         28,
         "Marketing",
         60000.0,
         6000.0
        ],
        [
         3,
         "Bob",
         32,
         "Finance",
         55000.0,
         5500.0
        ],
        [
         4,
         "Sarah",
         29,
         "Sales",
         52000.0,
         5200.0
        ],
        [
         5,
         "Mike",
         31,
         "Finance",
         58000.0,
         5800.0
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "id",
         "type": "\"integer\""
        },
        {
         "metadata": "{}",
         "name": "name",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "age",
         "type": "\"integer\""
        },
        {
         "metadata": "{}",
         "name": "department",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "salary",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "bonus",
         "type": "\"double\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "employeeDF.display()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "ca912f30-4879-4152-9d0f-06e94fad5ca6",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>department</th><th>totalSalaryByDept</th><th>rank</th></tr></thead><tbody><tr><td>Finance</td><td>113000.0</td><td>1</td></tr><tr><td>Sales</td><td>102000.0</td><td>2</td></tr><tr><td>Marketing</td><td>60000.0</td><td>3</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         "Finance",
         113000.0,
         1
        ],
        [
         "Sales",
         102000.0,
         2
        ],
        [
         "Marketing",
         60000.0,
         3
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "department",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "totalSalaryByDept",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "rank",
         "type": "\"integer\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "#Find the top most department having highest salary\n",
    "windowSpecDept=Window.orderBy(desc(\"totalSalaryByDept\"))\n",
    "department_rnk = highestTotalSalaryByDept\\\n",
    "    .withColumn(\"rank\",rank().over(windowSpecDept))\n",
    "\n",
    "department_rnk.display()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "27cfde07-6591-4996-b808-68b139bef205",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>department</th></tr></thead><tbody><tr><td>Finance</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         "Finance"
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "department",
         "type": "\"string\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "topdeptSalary=department_rnk.\\\n",
    "    filter(col(\"rank\")==1)\\\n",
    "    .select(\"department\")\n",
    "\n",
    "topdeptSalary.display()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "2891c1a6-c4fb-4c0e-8fd5-d87eb3cd3489",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "##6. Filter the DataFrame to keep only employees aged 30 or above and working in the \"Sales\" department"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "4d2945ad-e907-4d1f-a949-12471a46010e",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>id</th><th>name</th><th>age</th><th>department</th><th>salary</th><th>bonus</th></tr></thead><tbody><tr><td>1</td><td>John</td><td>30</td><td>Sales</td><td>50000.0</td><td>5000.0</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         1,
         "John",
         30,
         "Sales",
         50000.0,
         5000.0
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "id",
         "type": "\"integer\""
        },
        {
         "metadata": "{}",
         "name": "name",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "age",
         "type": "\"integer\""
        },
        {
         "metadata": "{}",
         "name": "department",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "salary",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "bonus",
         "type": "\"double\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "#Filter the DataFrame to keep only employees aged 30 or above and working in the \"Sales\" department\n",
    "empFilteredDF= employeeDF.\\\n",
    "    filter((col(\"age\")>=30) & (col(\"department\")==\"Sales\"))\n",
    "\n",
    "empFilteredDF.display()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "439205b7-c074-4cdb-8677-0b644a0b4332",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "##7. Calculate the difference between each employee's salary and the average salary of their respective department"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "433a638c-232f-4d34-8d49-d5922456d0c6",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>id</th><th>name</th><th>age</th><th>department</th><th>salary</th><th>bonus</th><th>AvgSalarybyDept</th></tr></thead><tbody><tr><td>3</td><td>Bob</td><td>32</td><td>Finance</td><td>55000.0</td><td>5500.0</td><td>56500.0</td></tr><tr><td>5</td><td>Mike</td><td>31</td><td>Finance</td><td>58000.0</td><td>5800.0</td><td>56500.0</td></tr><tr><td>2</td><td>Alice</td><td>28</td><td>Marketing</td><td>60000.0</td><td>6000.0</td><td>60000.0</td></tr><tr><td>1</td><td>John</td><td>30</td><td>Sales</td><td>50000.0</td><td>5000.0</td><td>51000.0</td></tr><tr><td>4</td><td>Sarah</td><td>29</td><td>Sales</td><td>52000.0</td><td>5200.0</td><td>51000.0</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         3,
         "Bob",
         32,
         "Finance",
         55000.0,
         5500.0,
         56500.0
        ],
        [
         5,
         "Mike",
         31,
         "Finance",
         58000.0,
         5800.0,
         56500.0
        ],
        [
         2,
         "Alice",
         28,
         "Marketing",
         60000.0,
         6000.0,
         60000.0
        ],
        [
         1,
         "John",
         30,
         "Sales",
         50000.0,
         5000.0,
         51000.0
        ],
        [
         4,
         "Sarah",
         29,
         "Sales",
         52000.0,
         5200.0,
         51000.0
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "id",
         "type": "\"integer\""
        },
        {
         "metadata": "{}",
         "name": "name",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "age",
         "type": "\"integer\""
        },
        {
         "metadata": "{}",
         "name": "department",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "salary",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "bonus",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "AvgSalarybyDept",
         "type": "\"double\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "#Calculate the difference between each employee's salary and the average salary of their respective department\n",
    "employeeDFavgSal =employeeDF.\\\n",
    "    withColumn(\"AvgSalarybyDept\",avg(col(\"salary\")).over(Window.partitionBy(\"department\")))\n",
    "employeeDFavgSal.display()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "5be126b0-f207-493e-8f46-2daa4635d0e4",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>id</th><th>name</th><th>age</th><th>department</th><th>salary</th><th>bonus</th><th>AvgSalarybyDept</th><th>salarydiff</th></tr></thead><tbody><tr><td>3</td><td>Bob</td><td>32</td><td>Finance</td><td>55000.0</td><td>5500.0</td><td>56500.0</td><td>-1500.0</td></tr><tr><td>5</td><td>Mike</td><td>31</td><td>Finance</td><td>58000.0</td><td>5800.0</td><td>56500.0</td><td>1500.0</td></tr><tr><td>2</td><td>Alice</td><td>28</td><td>Marketing</td><td>60000.0</td><td>6000.0</td><td>60000.0</td><td>0.0</td></tr><tr><td>1</td><td>John</td><td>30</td><td>Sales</td><td>50000.0</td><td>5000.0</td><td>51000.0</td><td>-1000.0</td></tr><tr><td>4</td><td>Sarah</td><td>29</td><td>Sales</td><td>52000.0</td><td>5200.0</td><td>51000.0</td><td>1000.0</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         3,
         "Bob",
         32,
         "Finance",
         55000.0,
         5500.0,
         56500.0,
         -1500.0
        ],
        [
         5,
         "Mike",
         31,
         "Finance",
         58000.0,
         5800.0,
         56500.0,
         1500.0
        ],
        [
         2,
         "Alice",
         28,
         "Marketing",
         60000.0,
         6000.0,
         60000.0,
         0.0
        ],
        [
         1,
         "John",
         30,
         "Sales",
         50000.0,
         5000.0,
         51000.0,
         -1000.0
        ],
        [
         4,
         "Sarah",
         29,
         "Sales",
         52000.0,
         5200.0,
         51000.0,
         1000.0
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "id",
         "type": "\"integer\""
        },
        {
         "metadata": "{}",
         "name": "name",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "age",
         "type": "\"integer\""
        },
        {
         "metadata": "{}",
         "name": "department",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "salary",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "bonus",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "AvgSalarybyDept",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "salarydiff",
         "type": "\"double\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "empSalDiff=employeeDFavgSal.withColumn(\"salarydiff\",col(\"salary\")-col(\"AvgSalarybyDept\"))\n",
    "empSalDiff.display()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "fc409c11-1878-4386-a785-0f833f924748",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "##8. Calculate the sum of salaries for employees whose names start with the letter \"J\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "2340daeb-88e0-44e6-a72a-63bd0e901946",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>TotalSalary</th></tr></thead><tbody><tr><td>50000.0</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         50000.0
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "TotalSalary",
         "type": "\"double\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "empFilteredName = employeeDF.filter(col(\"name\").startswith(\"J\")).\\\n",
    "  agg(sum(\"salary\").alias(\"TotalSalary\")\n",
    ")\n",
    "\n",
    "empFilteredName.display()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "a62e5f47-abff-4ba4-b685-d0b64ff978b3",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "##9. Sort the DataFrame based on the \"age\" column in ascending order and then by \"salary\" column in descending order"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "0ab752da-e8b5-4d92-b9b4-d7826286b975",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>id</th><th>name</th><th>age</th><th>department</th><th>salary</th><th>bonus</th></tr></thead><tbody><tr><td>2</td><td>Alice</td><td>28</td><td>Marketing</td><td>60000.0</td><td>6000.0</td></tr><tr><td>4</td><td>Sarah</td><td>29</td><td>Sales</td><td>52000.0</td><td>5200.0</td></tr><tr><td>1</td><td>John</td><td>30</td><td>Sales</td><td>50000.0</td><td>5000.0</td></tr><tr><td>5</td><td>Mike</td><td>31</td><td>Finance</td><td>58000.0</td><td>5800.0</td></tr><tr><td>3</td><td>Bob</td><td>32</td><td>Finance</td><td>55000.0</td><td>5500.0</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         2,
         "Alice",
         28,
         "Marketing",
         60000.0,
         6000.0
        ],
        [
         4,
         "Sarah",
         29,
         "Sales",
         52000.0,
         5200.0
        ],
        [
         1,
         "John",
         30,
         "Sales",
         50000.0,
         5000.0
        ],
        [
         5,
         "Mike",
         31,
         "Finance",
         58000.0,
         5800.0
        ],
        [
         3,
         "Bob",
         32,
         "Finance",
         55000.0,
         5500.0
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "id",
         "type": "\"integer\""
        },
        {
         "metadata": "{}",
         "name": "name",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "age",
         "type": "\"integer\""
        },
        {
         "metadata": "{}",
         "name": "department",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "salary",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "bonus",
         "type": "\"double\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "empSortDF=employeeDF.sort(\"age\",desc(\"salary\"))\n",
    "\n",
    "empSortDF.display()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "468d1208-9895-4b38-bc1f-25fa663efde0",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "##10. Replace the department name \"Finance\" with \"Financial Services\" in the DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "2f49226c-e110-446c-a3fe-0f351bab781c",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>id</th><th>name</th><th>age</th><th>department</th><th>salary</th><th>bonus</th></tr></thead><tbody><tr><td>1</td><td>John</td><td>30</td><td>Sales</td><td>50000.0</td><td>5000.0</td></tr><tr><td>2</td><td>Alice</td><td>28</td><td>Marketing</td><td>60000.0</td><td>6000.0</td></tr><tr><td>3</td><td>Bob</td><td>32</td><td>Financial Services</td><td>55000.0</td><td>5500.0</td></tr><tr><td>4</td><td>Sarah</td><td>29</td><td>Sales</td><td>52000.0</td><td>5200.0</td></tr><tr><td>5</td><td>Mike</td><td>31</td><td>Financial Services</td><td>58000.0</td><td>5800.0</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         1,
         "John",
         30,
         "Sales",
         50000.0,
         5000.0
        ],
        [
         2,
         "Alice",
         28,
         "Marketing",
         60000.0,
         6000.0
        ],
        [
         3,
         "Bob",
         32,
         "Financial Services",
         55000.0,
         5500.0
        ],
        [
         4,
         "Sarah",
         29,
         "Sales",
         52000.0,
         5200.0
        ],
        [
         5,
         "Mike",
         31,
         "Financial Services",
         58000.0,
         5800.0
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "id",
         "type": "\"integer\""
        },
        {
         "metadata": "{}",
         "name": "name",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "age",
         "type": "\"integer\""
        },
        {
         "metadata": "{}",
         "name": "department",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "salary",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "bonus",
         "type": "\"double\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "#Replace the department name \"Finance\" with \"Financial Services\" in the DataFrame\n",
    "\n",
    "empDeptUpdated = employeeDF.withColumn(\"department\",when(col(\"department\")==\"Finance\",\"Financial Services\").otherwise(col(\"department\")))\n",
    "\n",
    "empDeptUpdated.display()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "091d49c3-3644-42d2-afbd-523173fc7fef",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "#11. Calculate the percentage of total salary each employee contributes to their respective department"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "93ff71c1-dd9d-4e6c-bc74-ce9e8f5f3cf8",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>id</th><th>name</th><th>age</th><th>department</th><th>salary</th><th>bonus</th><th>Total Salary</th></tr></thead><tbody><tr><td>3</td><td>Bob</td><td>32</td><td>Finance</td><td>55000.0</td><td>5500.0</td><td>113000.0</td></tr><tr><td>5</td><td>Mike</td><td>31</td><td>Finance</td><td>58000.0</td><td>5800.0</td><td>113000.0</td></tr><tr><td>2</td><td>Alice</td><td>28</td><td>Marketing</td><td>60000.0</td><td>6000.0</td><td>60000.0</td></tr><tr><td>1</td><td>John</td><td>30</td><td>Sales</td><td>50000.0</td><td>5000.0</td><td>102000.0</td></tr><tr><td>4</td><td>Sarah</td><td>29</td><td>Sales</td><td>52000.0</td><td>5200.0</td><td>102000.0</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         3,
         "Bob",
         32,
         "Finance",
         55000.0,
         5500.0,
         113000.0
        ],
        [
         5,
         "Mike",
         31,
         "Finance",
         58000.0,
         5800.0,
         113000.0
        ],
        [
         2,
         "Alice",
         28,
         "Marketing",
         60000.0,
         6000.0,
         60000.0
        ],
        [
         1,
         "John",
         30,
         "Sales",
         50000.0,
         5000.0,
         102000.0
        ],
        [
         4,
         "Sarah",
         29,
         "Sales",
         52000.0,
         5200.0,
         102000.0
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "id",
         "type": "\"integer\""
        },
        {
         "metadata": "{}",
         "name": "name",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "age",
         "type": "\"integer\""
        },
        {
         "metadata": "{}",
         "name": "department",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "salary",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "bonus",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "Total Salary",
         "type": "\"double\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "#Calculate the percentage of total salary each employee contributes to their respective department\n",
    "employeeDFTotalSalDept = employeeDF.withColumn(\"Total Salary\",sum(\"salary\").over(Window.partitionBy(\"department\")))\n",
    "employeeDFTotalSalDept.display()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "162523d6-dd03-48bb-9cfe-e60df7e8cca6",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/html": [
       "<style scoped>\n",
       "  .table-result-container {\n",
       "    max-height: 300px;\n",
       "    overflow: auto;\n",
       "  }\n",
       "  table, th, td {\n",
       "    border: 1px solid black;\n",
       "    border-collapse: collapse;\n",
       "  }\n",
       "  th, td {\n",
       "    padding: 5px;\n",
       "  }\n",
       "  th {\n",
       "    text-align: left;\n",
       "  }\n",
       "</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>id</th><th>name</th><th>age</th><th>department</th><th>salary</th><th>bonus</th><th>Total Salary</th><th>Percentage_Contribution</th></tr></thead><tbody><tr><td>3</td><td>Bob</td><td>32</td><td>Finance</td><td>55000.0</td><td>5500.0</td><td>113000.0</td><td>48.67</td></tr><tr><td>5</td><td>Mike</td><td>31</td><td>Finance</td><td>58000.0</td><td>5800.0</td><td>113000.0</td><td>51.33</td></tr><tr><td>2</td><td>Alice</td><td>28</td><td>Marketing</td><td>60000.0</td><td>6000.0</td><td>60000.0</td><td>100.0</td></tr><tr><td>1</td><td>John</td><td>30</td><td>Sales</td><td>50000.0</td><td>5000.0</td><td>102000.0</td><td>49.02</td></tr><tr><td>4</td><td>Sarah</td><td>29</td><td>Sales</td><td>52000.0</td><td>5200.0</td><td>102000.0</td><td>50.98</td></tr></tbody></table></div>"
      ]
     },
     "metadata": {
      "application/vnd.databricks.v1+output": {
       "addedWidgets": {},
       "aggData": [],
       "aggError": "",
       "aggOverflow": false,
       "aggSchema": [],
       "aggSeriesLimitReached": false,
       "aggType": "",
       "arguments": {},
       "columnCustomDisplayInfos": {},
       "data": [
        [
         3,
         "Bob",
         32,
         "Finance",
         55000.0,
         5500.0,
         113000.0,
         48.67
        ],
        [
         5,
         "Mike",
         31,
         "Finance",
         58000.0,
         5800.0,
         113000.0,
         51.33
        ],
        [
         2,
         "Alice",
         28,
         "Marketing",
         60000.0,
         6000.0,
         60000.0,
         100.0
        ],
        [
         1,
         "John",
         30,
         "Sales",
         50000.0,
         5000.0,
         102000.0,
         49.02
        ],
        [
         4,
         "Sarah",
         29,
         "Sales",
         52000.0,
         5200.0,
         102000.0,
         50.98
        ]
       ],
       "datasetInfos": [],
       "dbfsResultPath": null,
       "isJsonSchema": true,
       "metadata": {},
       "overflow": false,
       "plotOptions": {
        "customPlotOptions": {},
        "displayType": "table",
        "pivotAggregation": null,
        "pivotColumns": null,
        "xColumns": null,
        "yColumns": null
       },
       "removedWidgets": [],
       "schema": [
        {
         "metadata": "{}",
         "name": "id",
         "type": "\"integer\""
        },
        {
         "metadata": "{}",
         "name": "name",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "age",
         "type": "\"integer\""
        },
        {
         "metadata": "{}",
         "name": "department",
         "type": "\"string\""
        },
        {
         "metadata": "{}",
         "name": "salary",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "bonus",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "Total Salary",
         "type": "\"double\""
        },
        {
         "metadata": "{}",
         "name": "Percentage_Contribution",
         "type": "\"double\""
        }
       ],
       "type": "table"
      }
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "employeeDFpercentSalDept = employeeDFTotalSalDept.withColumn(\n",
    "    \"Percentage_Contribution\", round((col(\"salary\") / col(\"Total Salary\")) * 100, 2)\n",
    ")\n",
    "\n",
    "employeeDFpercentSalDept.display()"
   ]
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "dashboards": [],
   "language": "python",
   "notebookMetadata": {
    "pythonIndentUnit": 4
   },
   "notebookName": "PySpark_Practice_Queries",
   "widgets": {}
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}