Unsolved/1412. Find the Quiet Students in All Exam (Hard).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "24f8e8c0-d56b-4821-a900-bbe934dce4d7",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "source": [
    "## Importing Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "243b190c-af0b-4bd1-9507-8e2be2fceca3",
     "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": "2d9dc49c-3f2c-4ff6-8e98-5e72a86b1bac",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "source": [
    "**1412. Find the Quiet Students in All Exam (Hard)**\n",
    "\n",
    "**Table: Student**\n",
    "\n",
    "| Column Name         | Type    |\n",
    "|---------------------|---------|\n",
    "| student_id          | int     |\n",
    "| student_name        | varchar |\n",
    "\n",
    "student_id is the primary key (column with unique values) for this table.\n",
    "student_name is the name of the student.\n",
    " \n",
    "**Table: Exam**\n",
    "\n",
    "| Column Name   | Type    |\n",
    "|---------------|---------|\n",
    "| exam_id       | int     |\n",
    "| student_id    | int     |\n",
    "| score         | int     |\n",
    "\n",
    "(exam_id, student_id) is the primary key (combination of columns with unique values) for this table.\n",
    "Each row of this table indicates that the student with student_id had a score points in the exam with id exam_id.\n",
    "\n",
    "A quiet student is the one who took at least one exam and did not score the highest or the lowest score.\n",
    "\n",
    "**Write a solution to report the students (student_id, student_name) being quiet in all exams. Do not return the student who has never taken any exam.**\n",
    "\n",
    "Return the result table ordered by student_id.\n",
    "\n",
    "The result format is in the following example.\n",
    "\n",
    "**Example 1:**\n",
    "\n",
    "**Input:** \n",
    "\n",
    "**Student table:**\n",
    "| student_id  | student_name  |\n",
    "|-------------|---------------|\n",
    "| 1           | Daniel        |\n",
    "| 2           | Jade          |\n",
    "| 3           | Stella        |\n",
    "| 4           | Jonathan      |\n",
    "| 5           | Will          |\n",
    "\n",
    "**Exam table:**\n",
    "\n",
    "| exam_id    | student_id   | score     |\n",
    "|------------|--------------|-----------|\n",
    "| 10         |     1        |    70     |\n",
    "| 10         |     2        |    80     |\n",
    "| 10         |     3        |    90     |\n",
    "| 20         |     1        |    80     |\n",
    "| 30         |     1        |    70     |\n",
    "| 30         |     3        |    80     |\n",
    "| 30         |     4        |    90     |\n",
    "| 40         |     1        |    60     |\n",
    "| 40         |     2        |    70     |\n",
    "| 40         |     4        |    80     |\n",
    "\n",
    "**Output:** \n",
    "| student_id  | student_name  |\n",
    "|-------------|---------------|\n",
    "| 2           | Jade          |\n",
    "\n",
    "**Explanation:** \n",
    "- For exam 1: Student 1 and 3 hold the lowest and high scores respectively.\n",
    "- For exam 2: Student 1 hold both highest and lowest score.\n",
    "- For exam 3 and 4: Studnet 1 and 4 hold the lowest and high scores respectively.\n",
    "- Student 2 and 5 have never got the highest or lowest in any of the exams.\n",
    "- Since student 5 is not taking any exam, he is excluded from the result. So, we only return the information of Student 2."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "5d66d20c-58e2-4ee9-8356-d736216372c3",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "students_data_1412 = [\n",
    "    (1, \"Daniel\"),\n",
    "    (2, \"Jade\"),\n",
    "    (3, \"Stella\"),\n",
    "    (4, \"Jonathan\"),\n",
    "    (5, \"Will\")\n",
    "]\n",
    "students_columns_1412 = [\"student_id\", \"student_name\"]\n",
    "students_df_1412 = spark.createDataFrame(students_data_1412, students_columns_1412)\n",
    "students_df_1412.show()\n",
    "\n",
    "exams_data_1412 = [\n",
    "    (10, 1, 70),\n",
    "    (10, 2, 80),\n",
    "    (10, 3, 90),\n",
    "    (20, 1, 80),\n",
    "    (30, 1, 70),\n",
    "    (30, 3, 80),\n",
    "    (30, 4, 90),\n",
    "    (40, 1, 60),\n",
    "    (40, 2, 70),\n",
    "    (40, 4, 80)\n",
    "]\n",
    "exams_columns_1412 = [\"exam_id\", \"student_id\", \"score\"]\n",
    "exams_df_1412 = spark.createDataFrame(exams_data_1412, exams_columns_1412)\n",
    "exams_df_1412.show()"
   ]
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "computePreferences": null,
   "dashboards": [],
   "environmentMetadata": {
    "base_environment": "",
    "environment_version": "1"
   },
   "inputWidgetPreferences": null,
   "language": "python",
   "notebookMetadata": {
    "pythonIndentUnit": 4
   },
   "notebookName": "1412. Find the Quiet Students in All Exam (Hard)",
   "widgets": {}
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}