window_function.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Top-3 earners in each department\n",
    "\n",
    "\n",
    "```sql\n",
    "SELECT \n",
    "    d.name AS department, \n",
    "    e.name AS employee, \n",
    "    e.salary AS salary \n",
    "FROM (\n",
    "    SELECT *, \n",
    "        DENSE_RANK() OVER (\n",
    "            PARTITION BY dept_id  \n",
    "            ORDER BY salary DESC \n",
    "        ) AS 'ranking'\n",
    "    FROM employee \n",
    ") AS e \n",
    "JOIN department AS d \n",
    "    ON d.id = e.dept_id \n",
    "WHERE e.ranking <= 3 \n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Top earner in each department \n",
    "\n",
    "```sql\n",
    "SELECT \n",
    "    department, \n",
    "    salary,\n",
    "    ROW_NUMBER() OVER (\n",
    "        PARTITION BY department\n",
    "        ORDER BY salary DESC \n",
    "    ) AS ranking \n",
    "FROM employee \n",
    "WHERE ranking = 1 \n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Delete all duplicate rows from the table \n",
    "\n",
    "```sql\n",
    "SELECT * \n",
    "FROM (\n",
    "    SELECT *,\n",
    "        ROW_NUMBER() OVER (\n",
    "            PARTITION BY *\n",
    "        ) as rn \n",
    "    FROM \n",
    "        my_table_name\n",
    ") AS x \n",
    "WHERE x.rn = 1 \n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  }
 ],
 "metadata": {
  "language_info": {
   "name": "python"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}