SQL_cheet_sheet .ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "8a25fd6e",
   "metadata": {},
   "source": [
    "# Theoretical \n",
    "\n",
    "__Database, Table, RDMS, SQL__\n",
    "\n",
    "__Normalization in SQL: 1NF, 2NF, 3NF, BCNF, 4NF, 5NF__\n",
    "\n",
    "__Types of SQL commands: DDL, DML, DCL, TCL, DQL__\n",
    "\n",
    "__Order of Execution in SQL__"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "8f1879ea",
   "metadata": {},
   "source": [
    "# Data Definition Language (DDL)\n",
    "\n",
    "__CREATE__\n",
    "\n",
    "```sql\n",
    "CREATE TABLE tab_name (\n",
    "    col_1 [DATA_TYPE_1] [CONSTRAINT_1],  \n",
    "    col_2 [DATA_TYPE_2] [CONSTRAINT_2], \n",
    "    CONSTRAINT [CONSTRAINT_3]\n",
    ");\n",
    "```\n",
    "\n",
    "__DROP__\n",
    "\n",
    "```sql\n",
    "DROP TABLE tab_name;\n",
    "```\n",
    "\n",
    "\n",
    "__ALTER__\n",
    "\n",
    "```sql\n",
    "ALTER TABLE tab_name RENAME TO new_tab_name; \n",
    "```\n",
    "\n",
    "```sql\n",
    "ALTER TABLE tab_name RENAME COLUMN col_name TO new_col_name; \n",
    "```\n",
    "\n",
    "```sql\n",
    "ALTER TABLE tab_name DROP COLUMN (\n",
    "    col_1, col_2\n",
    "); \n",
    "```\n",
    "\n",
    "```sql\n",
    "ALTER TABLE tab_name ADD COLUMN (\n",
    "    col_1 [DATA_TYPE_1] [CONSTRAINT_1], \n",
    "    col_2 [DATA_TYPE_1]\n",
    ");\n",
    "```\n",
    "\n",
    "\n",
    "```sql\n",
    "ALTER TABLE tab_name ALTER COLUMN col_name TYPE [DATA_TYPE]; \n",
    "```\n",
    "\n",
    "```sql\n",
    "ALTER TABLE tab_name ADD CONSTRAINT col_name [CONSTRAINT_]; \n",
    "```\n",
    "\n",
    "```sql\n",
    "ALTER TABLE tab_name MODIFY COLUMN (\n",
    "    col_1 [DATA_TYPE_1] [CONSTRAINT_1], \n",
    "    col_2 [DATA_TYPE_1]\n",
    ");\n",
    "```\n",
    "\n",
    "\n",
    "__TRUNCATE__\n",
    "\n",
    "```sql\n",
    "TRUNCATE TABLE tab_name;\n",
    "```"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "1b7ba683",
   "metadata": {},
   "source": [
    "# Data Manipulation Language (DML)\n",
    "\n",
    "__INSERT__\n",
    "\n",
    "```sql\n",
    "INSERT INTO tab_name (col_1, col_2) VALUES \n",
    "    ([VAL_11], [VAL_12]), \n",
    "    ([VAL_21], [VAL_22]);\n",
    "```\n",
    "\n",
    "__UUDATE__\n",
    "\n",
    "```sql\n",
    "UPDATE tab_name SET \n",
    "    col_1 = [VAL_1], col_2 = [VAL_2] \n",
    "WHERE [CONDITION];\n",
    "```\n",
    "\n",
    "__DELETE__\n",
    "\n",
    "```sql\n",
    "DELETE FROM tab_name;\n",
    "```\n",
    "\n",
    "```sql\n",
    "DELETE FROM tab_name WHERE [CONDITION];\n",
    "```\n",
    "\n",
    "__MERGE__\n",
    "\n",
    "```sql\n",
    "USE tab_merge GO\n",
    "    MERGE tab_1 AS t1\n",
    "    USING tab_2 AS t2\n",
    "    ON t1.id_1 = t2.id_2\n",
    "\n",
    "    WHEN NOT MATCHED BY tab_1 THEN\n",
    "        INSERT (col_1, col_2) \n",
    "        VALUES (t2.[VAL_1], t2.[VAL_2])\n",
    "        \n",
    "    WHEN MATCHED THEN UPDATE SET\n",
    "        t1.col_1 = t2.col_1\n",
    "        t1.col_2 = t2.col_2;\n",
    "```\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "89bbc3e7",
   "metadata": {},
   "source": [
    "# Data Control Language (DCL)\n",
    "\n",
    "__GRANT__\n",
    "\n",
    "```sql\n",
    "GRANT SELECT, UPDATE, CREATE tab_name TO user_1, user_2;\n",
    "```\n",
    "\n",
    "__REVOKE__\n",
    "\n",
    "```sql\n",
    "REVOKE UPDATE, DELETE tab_name TO user_1, user_2;\n",
    "```"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "11929f7e",
   "metadata": {},
   "source": [
    "# Transaction Control Language (TCL)\n",
    "\n",
    "__COMMIT__\n",
    "\n",
    "```sql\n",
    "DELETE FROM tab_name WHERE [CONDITION] \n",
    "COMMIT; \n",
    "```\n",
    "\n",
    "__ROLLBACK__\n",
    "\n",
    "```sql\n",
    "DELETE FROM tab_name WHERE [CONDITION]\n",
    "ROLLBACK; \n",
    "```\n",
    "\n",
    "__SAVEPOINT__\n",
    "\n",
    "```sql\n",
    "SAVEPOINT [SAVEPOINT_NAME];\n",
    "```\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "9b38b218",
   "metadata": {},
   "source": [
    "# Data Query Language (DQL)\n",
    "\n",
    "```sql\n",
    "SELECT col_name FROM tab_name WHERE [CONDITION] \n",
    "```"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "d1c3d5b8",
   "metadata": {},
   "source": [
    "# Data Types\n",
    "\n",
    "__Top 5 data types__\n",
    "\n",
    "```sql\n",
    "CREATE TABLE tab_name (\n",
    "    col_1 VARCHAR(30), \n",
    "    col_2 INT, \n",
    "    col_3 DATE, \n",
    "    col_4 BOOL, \n",
    "    col_5 FLOAT, \n",
    ");\n",
    "```\n",
    "\n",
    "__Additional data types__\n",
    "\n",
    "```sql\n",
    "CREATE TABLE tab_name (\n",
    "    col_6 DECIMAL(6, 2), \n",
    "    col_7 TEXT, \n",
    "    col_8 NUMBER(3), \n",
    "    col_4 TIMESTAMP, \n",
    "); \n",
    "```\n",
    "\n",
    "__Auto-increment and Index__\n",
    "\n",
    "```sql\n",
    "CREATE TABLE tab_name (\n",
    "    col_1 [DATA_TYPE_1] AUTO_INCREMENT, \n",
    ");\n",
    "```\n",
    "\n",
    "```sql\n",
    "CREATE INDEX index_name ON tab_name (col_1, col_2);\n",
    "```\n",
    "\n",
    "```sql\n",
    "DROP INDEX index_name; \n",
    "```"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "8c9d5464",
   "metadata": {},
   "source": [
    "# Constraints\n",
    "\t\n",
    "__Basic constraints__\n",
    "\n",
    "```sql\n",
    "CREATE TABLE tab_name (\n",
    "    col_1 [DATA_TYPE_1] UNIQUE, \n",
    "    col_2 [DATA_TYPE_2] NOT NULL, \n",
    "    col_3 [DATA_TYPE_3] DEFAULT [VAL_1], \n",
    "    col_4 [DATA_TYPE_4] CHECK (col_4 > 0), \n",
    ");\n",
    "```\n",
    "\n",
    "\n",
    "__PRIMARY KEY, FOREIGN KEY__ \n",
    "\n",
    "```sql\n",
    "CREATE TABLE tab_name (\n",
    "    col_1 [DATA_TYPE_1],  \n",
    "    col_2 [DATA_TYPE_2], \n",
    "    col_3 [DATA_TYPE_3], \n",
    "    CONSTRAINT PRIMARY KEY (col_1), \n",
    "    CONSTRAINT FOREIGN KEY (col_2) \n",
    "        REFERENCES tab_other (col_other), \n",
    "    CONSTRAINT FOREIGN KEY (col_3)\n",
    "        REFERENCES tab_another (col_another)\n",
    "); \n",
    "```\n",
    "\n",
    "\n",
    "__Multiple constraints__\n",
    "\n",
    "```sql\n",
    "ALTER TABLE tab_name \n",
    "    ADD CONSTRAINT constraint_name_1 UNIQUE (col_1, col_2), \n",
    "    ADD CONSTRAINT constraint_name_2 NOT NULL (col_2, col_3)\n",
    "    ADD CONSTRAINT constraint_name_3 CHECK(col_4 IN ('A', 'B')), \n",
    "    ADD COLUMN col_4 TIMESTAMP DEFAULT NOW() ON UPDATE NOW(); \n",
    "```\n",
    "\n",
    "```sql\n",
    "ALTER TABLE tab_name \n",
    "    DROP CONSTRAINT constraint_name_1\n",
    "    DROP CONSTRAINT constraint_name_2; \n",
    "```"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "9ae1b929",
   "metadata": {},
   "source": [
    "# Operators\n",
    "\t\n",
    "__Arithmetic operator__\n",
    "\n",
    "```sql\n",
    "SELECT col_1 \n",
    ", col_1 + col_2 AS total \n",
    ", col_1 - col_2 AS difference\n",
    ", col_1 * col_2 AS product \n",
    ", col_1 / col_2 AS division \n",
    ", col_1 % col_2 AS modulus \n",
    "FROM tab_name; \n",
    "```\n",
    "\n",
    "\n",
    "__Logical operator__\n",
    "\n",
    "```sql\n",
    "SELECT col_name FROM tab_name \n",
    "WHERE NOT [CONDITION_1] \n",
    "    AND [CONDITION_2]\n",
    "    OR [CONDITION_3];\n",
    "```\n",
    "\n",
    "\n",
    "__Comparison operator__\n",
    "\n",
    "```sql\n",
    "SELECT col_1 FROM tab_name \n",
    "WHERE (col_1 = [VAL_1])\n",
    "    AND (col_2 > [VAL_2])\n",
    "    OR (col_3 <= [VAL_3])\n",
    "    AND (col_4 <> [VAL_4]); \n",
    "```\n",
    "\n",
    "\n",
    "\n",
    "__IN, BETWEEN, LIKE__\n",
    "\n",
    "```sql\n",
    "SELECT col_1 FROM tab_name \n",
    "WHERE col_1 IN ([VAL_1], [VAL_2], ..., [VAL_N])\n",
    "    AND col_2 BETWEEN [VAL_1] AND [VAL_2]\n",
    "    AND col_3 LIKE [REGEX]; \n",
    "```\n",
    "\n",
    "\n",
    "__Set operators__\n",
    "\t\n",
    "```sql\n",
    "SELECT col_a1, col_b1 FROM tab_1 \n",
    "UNION \n",
    "SELECT col_a2, col_b2 FROM tab_2;\n",
    "```\n",
    "\n",
    "```sql\n",
    "SELECT col_a1, col_b1 FROM tab_1 WHERE [CONDITION_1]\n",
    "UNION ALL\n",
    "SELECT col_a2, col_b2 FROM tab_2 WHERE [CONDITION_2];\n",
    "```\n",
    "\n",
    "```sql\n",
    "SELECT col_a1, col_b1 FROM tab_1\n",
    "INTERSECTION\n",
    "SELECT col_a2, col_b2 FROM tab_2\n",
    "MINUS\n",
    "SELECT col_a3, col_b3 FROM tab_3; \n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bc50d7eb",
   "metadata": {},
   "source": [
    "# Conditional \n",
    "\n",
    "__Simple case statement__\n",
    "\n",
    "```sql\n",
    "SELECT col_1\n",
    ", CASE @col_2 \n",
    "    WHEN [CONDITION_1] THEN [VAL_1] \n",
    "    WHEN [CONDITION_2] THEN [VAL_2]\n",
    "    ELSE [VAL_3]\n",
    "  END AS col_2_cat \n",
    "FROM tab_name; \n",
    "```\n",
    "\t \n",
    "__Nested case statement__\n",
    "\n",
    "```sql\n",
    "SELECT col_1\n",
    ", CASE @col_2 \n",
    "    WHEN [CONDITION_1] THEN [VAL_1] \n",
    "    ELSE CASE\n",
    "        WHEN [CONDITION_21] THEN [VAL_21]\n",
    "        WHEN [CONDITION_22] THEN CASE\n",
    "            WHEN [CONDITION_221] THEN [VAL_221]\n",
    "            ELSE [VAL_222]\n",
    "        END \n",
    "        ELSE [VAL_23]\n",
    "    END \n",
    "  END AS col_2_cat \n",
    "FROM tab_name; \n",
    "```"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "0f90168c",
   "metadata": {},
   "source": [
    "# In-built functions\n",
    "\n",
    "__Aggrigate functions__\n",
    "\n",
    "\n",
    "```sql\n",
    "SELECT COUNT(*) AS n_rows \n",
    ", COUNT(DISTINCT col_1) AS n_1\n",
    ", SUM(col_1) AS tot_1\n",
    ", MIN(col_1) AS min_1\n",
    ", MAX(col_1) AS max_1\n",
    ", AVG(col_1) AS avg_1\n",
    "FROM tab_name;\n",
    "```\n",
    "\n",
    "\n",
    "__String functions__  \n",
    "\n",
    "$\\qquad \\color{red}{\\text{SUBSTRING, POSITION, COLESCE}}$ \n",
    "\n",
    "__Date functions__ \n",
    "\n",
    "$\\qquad \\color{red}{\\text{EXTRACT, TO_DATE}}$"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d6d65414",
   "metadata": {},
   "source": [
    "# Important SQL clause\n",
    "\t\n",
    "__DISTINCT / ORDER BY / LIMIT__\n",
    "\n",
    "```sql\n",
    "SELECT DISTINCT col_name \n",
    "FROM tab_name \n",
    "ORDER BY col_name DESC \n",
    "LIMIT 10; \n",
    "```\n",
    "\n",
    "\n",
    "__Aggrigate with GROUP BY__\n",
    "\n",
    "```sql\n",
    "SELECT col_1\n",
    ", COUNT(col_1) AS n_per_categories \n",
    ", AVG(col_1) AS avg_per_categories\n",
    "FROM tab_name \n",
    "GROUP BY col_1 \n",
    "ORDER BY col_1\n",
    "```\n",
    "\n",
    "\n",
    "__GROUP BY / HAVING__\n",
    "```sql\n",
    "SELECT col_name, COUNT(*) \n",
    "FROM tab_name\n",
    "GROUP BY col_name \n",
    "HAVING COUNT(*) > [VAL_1]\n",
    "ORDER BY COUNT(*) ASC \n",
    "LIMIT 5;\n",
    "```"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "dc5da39d",
   "metadata": {},
   "source": [
    "# Table Joins\n",
    "\n",
    "__Two ways to join tables__\n",
    "\n",
    "```sql\n",
    "SELECT * \n",
    "FROM tab_1 AS t1 \n",
    "JOIN tab_2 AS t2 \n",
    "    ON t1.key_1 = t2.key_2;\n",
    "```\n",
    "\n",
    "\n",
    "```sql\n",
    "SELECT * \n",
    "FROM tab_1 AS t1 \n",
    "JOIN tab_2 AS t2 \n",
    "    WHERE t1.key_1 = t2.key_2;\n",
    "```\n",
    "\n",
    "__INNER JOIN__ $ A \\cap B $\n",
    "\n",
    "```sql\n",
    "SELECT * \n",
    "FROM tab_1 AS t1 \n",
    "INNER JOIN tab_2 AS t2 \n",
    "    ON t1.key_1 = t2.key_2;\n",
    "```\n",
    "\n",
    "__LEFT JOIN__ $A \\cup (A \\cap B )$ \n",
    "\n",
    "```sql\n",
    "SELECT * \n",
    "FROM tab_1 AS t1 \n",
    "LEFT JOIN tab_2 AS t2 \n",
    "    ON t1.key_1 = t2.key_2;\n",
    "```\n",
    "\n",
    "__RIGHT JOIN__ $ (A \\cap B) \\cup B $ \n",
    "\n",
    "```sql\n",
    "SELECT * \n",
    "FROM tab_1 AS t1 \n",
    "RIGHT JOIN tab_2 AS t2 \n",
    "    ON t1.key_1 = t2.key_2;\n",
    "```\n",
    "\n",
    "\n",
    "__FULL (OUTER) JOIN__ $ A \\cup B $\n",
    "\n",
    "```sql\n",
    "SELECT * \n",
    "FROM tab_1 AS t1 \n",
    "FULL JOIN tab_2 AS t2 \n",
    "    ON t1.key_1 = t2.key_2;\n",
    "```\n",
    "\n",
    "\n",
    "\n",
    "__CROSS JOIN__ $ A \\times B $\n",
    "\n",
    "```sql\n",
    "SELECT t1.col_1, t2.col_2 \n",
    "FROM tab_1 AS t1 \n",
    "CROSS JOIN tab_2 AS t2 -- no join condition \n",
    "```\n",
    "\n",
    "\n",
    "__SELF JOIN__ $ A \\circ A $\n",
    "\n",
    "```sql\n",
    "SELECT parent.name, child.name \n",
    "FROM tab_name AS parent \n",
    "JOIN tab_name AS child \n",
    "    ON parent.id = child.parent_id; \n",
    "```"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "c4b3f3a0",
   "metadata": {},
   "source": [
    "# Sub-Queries\n",
    "__Scalar__ \n",
    "\n",
    "```sql\n",
    "SELECT * FROM tab_name \n",
    "WHERE col_1 > (\n",
    "    SELECT AVG(col_1) FROM tab_name \n",
    "); \n",
    "```\n",
    "\n",
    "\n",
    "```sql\n",
    "SELECT * FROM tab_name AS t \n",
    "JOIN (SELECT AVG(col_1) AS col_1_avg FROM tab_name) AS t_avg\n",
    "    ON t.col_1 > t_avg.col_1_avg; \n",
    "```\n",
    "\n",
    "__Multi row__\n",
    "\n",
    "```sql\n",
    "SELECT * FROM tab_1 AS t1 \n",
    "WHERE t1.col_1 IN (\n",
    "    SELECT DISTINCT t2.col_1 FROM tab_2 AS t2\n",
    "    WHERE [CONDITION]\n",
    "); \n",
    "```\n",
    "\n",
    "\n",
    "```sql\n",
    "SELECT * FROM tab_name \n",
    "WHERE (col_1, col_2) IN (\n",
    "    SELECT col_1, AVG(col_2) FROM tab_name \n",
    "    GROUP BY col_1 \n",
    ");\n",
    "```\n",
    "\n",
    "\n",
    "\n",
    "__Correlated__ \n",
    "\n",
    "```sql\n",
    "SELECT * FROM tab_name AS t\n",
    "WHERE col_1 > (\n",
    "    SELECT AVG(col_1) FROM tab_name \n",
    "    WHERE col_2 = t.col_2 \n",
    ");\n",
    "```\n",
    "\n",
    "```sql\n",
    "SELECT * FROM tab_1 AS t\n",
    "WHERE col_1 > (\n",
    "    SELECT AVG(col_1) FROM tab_2 \n",
    "    WHERE col_2 = t.col_2 \n",
    ");\n",
    "```\n",
    "\n",
    "Tips: avoid correlated sub-query if possible.\n",
    "\n",
    "__ALL / ANY / EXISTS__\n",
    "\n",
    "```sql\n",
    "SELECT * FROM tab_name \n",
    "WHERE [CONDITION]\n",
    "    AND col_1 > ALL (\n",
    "        SELECT col_2 FROM tab_name \n",
    "    )\n",
    "    OR col_2 < ANY (\n",
    "        SELECT col_3 FROM tab_name \n",
    "    );\n",
    "```\n",
    "\n",
    "\n",
    "```sql\n",
    "SELECT * FROM tab_name \n",
    "WHERE EXIST (\n",
    "        SELECT 1 FROM tab_name \n",
    "        WHERE [CONDITION_1]\n",
    "    )\n",
    "    AND NOT EXIST (\n",
    "        SELECT 1 FROM tab_name \n",
    "        WHERE [CONDITION_2]\n",
    "    );\n",
    "```\n",
    "\n",
    "Tips: `ALL` can potentially be avoided using `MAX`. \n",
    "\n",
    "Tips: `EXISTS` can potentially be avoided using `IN`."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "a96c8eb8",
   "metadata": {},
   "source": [
    "# Create things  \n",
    "\n",
    "__Common Table Expression (CTE)__ \n",
    "\n",
    "```sql\n",
    "WITH cte_table AS (\n",
    "    SELECT * FROM tab_1 WHERE [CONDITION_1]\n",
    ")\n",
    "SELECT * FROM cte_table; \n",
    "```\n",
    "\n",
    "\n",
    "__Views__ \n",
    "\n",
    "```sql\n",
    "CREATE OR REPLACE VIEW view_name AS\n",
    "\tSELECT * FROM tab_name WHERE [CONDITION];\n",
    "```\n",
    "\t\n",
    "\n",
    "__Save__\n",
    "```sql\n",
    "CREATE OR REPLACE TABLE new_tab_name AS \n",
    "    SELECT * FROM tab_name WHERE [CONDITION]; \n",
    "```\n",
    "\n",
    "\n",
    "__Temporary table__ \n",
    "\n",
    "```sql\n",
    "CREATE OR REPLACE TEMPORARY TABLE temp_table AS (\n",
    "    col_1 [DATA_TYPE_1], \n",
    "    col_2 [DATA_TYPE_2] [CONSTRAINT_2]\n",
    "); \n",
    "```"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "66d5cf5a",
   "metadata": {},
   "source": [
    "# Window functions\n",
    "\t\n",
    "\n",
    "__With usual aggrigate__\n",
    "\n",
    "```sql\n",
    "SELECT col_1, col_2 \n",
    ", AVG(col_1) OVER (\n",
    "    PARTITION BY col_2  \n",
    ") AS avg1_part2  \n",
    "FROM tab_name;\n",
    "```\n",
    "\n",
    "__RANK_NUMBER, RANK, DENSE_RANK__\n",
    "\n",
    "`ROW_NUMBER{A, B, B, C} -> {1, 2, 3, 4}`\n",
    "\n",
    "`RANK{A, B, B, C} -> {1, 2, 2, 4}`\n",
    "\n",
    "`DENSE_RANK{A, B, B, C} -> {1, 2, 2, 3}`\n",
    "\n",
    "\n",
    "```sql\n",
    "SELECT col_1, col_2, col_3  \n",
    ", ROW_NUMBER() OVER (\n",
    "    PARTITION BY col_2 \n",
    "    ORDER BY col_3 \n",
    ") AS row_num\n",
    "FROM tab_name;\n",
    "```\n",
    "\n",
    "Combining multiple window functions \n",
    "\n",
    "```sql\n",
    "SELECT col_1, col_2, col_3  \n",
    ", ROW_NUMBER() OVER w AS row_num\n",
    ", RANK() OVER w AS rank\n",
    ", DENSE_RANK() OVER w AS dense_rank\n",
    "FROM tab_name\n",
    "WHERE [CONDITION]\n",
    "WINDOW w AS (\n",
    "    PARTITION BY col_2 \n",
    "    ORDER BY col_3 \n",
    ");\n",
    "```\n",
    "\n",
    "\n",
    "Select top-10 from each group \n",
    "\n",
    "```sql\n",
    "SELECT * FROM (\n",
    "    SELECT col_1, col_2, col3\n",
    "    , RANK() OVER (\n",
    "        PARTITION BY col_1 \n",
    "        ORDER BY col_2 DESC \n",
    "    ) AS rank \n",
    "    FROM tab_name \n",
    ") AS x \n",
    "WHERE x.rank <= 10;\n",
    "```\n",
    "\n",
    "\n",
    "\n",
    "__LEAD, LAG__\n",
    "\n",
    "`LAG{A, B, C, D} -> {NULL, A, B, C}`\n",
    "\n",
    "`LEAD{A, B, C, D} -> {B, C, D, NULL}`\n",
    "\n",
    "\n",
    "```sql\n",
    "SELECT *\n",
    ", LAG(col_1, [STEP_LAG], [DEFAULT_VAL]) OVER w AS lag_col_1 \n",
    ", LEAD(col_1, [STEP_LEAD], [DEFAULT_VAL]) OVER w AS lead_col_1 \n",
    "FROM tab_name \n",
    "WINDOW w AS (\n",
    "    PARTITION BY col_2 \n",
    "    ORDER BY col_3 \n",
    "    [RANGE BETWEEN UNBOUNDED PRECEDING\n",
    "        AND CURRENT FOLLOWING] -- default frame \n",
    "); \n",
    "```\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "__FIRST_VALUE, LAST_VALUE, NTH_VALUE__\n",
    "\n",
    "```sql\n",
    "SELECT *\n",
    ", FIRST_VALUE(col_1) OVER w AS col_1_first \n",
    ", LAST_VALUE(col_1) OVER w AS col_1_last \n",
    "FROM tab_name\n",
    "WINDOW w AS (\n",
    "    PARTITION BY col_2 \n",
    "    ORDER BY col_3 \n",
    "    RANGE BETWEEN UNBOUNDED PRECEDING\n",
    "        AND UNBOUNDED FOLLOWING -- frame for last value \n",
    "); \n",
    "```\n",
    "\n",
    "\n",
    "```sql\n",
    "SELECT *\n",
    ", NTH_VALUE(col_1, [N]) OVER w AS col_1_nth \n",
    ", NTH_VALUE(col_1, -[N]) OVER w AS col_1_nth_last \n",
    "FROM tab_name\n",
    "WINDOW w AS (\n",
    "    PARTITION BY col_2 \n",
    "    ORDER BY col_3 DESC \n",
    "    ROWS BETWEEN 5 PRECEDING\n",
    "        AND 5 FOLLOWING -- custom frame \n",
    "); \n",
    "```\n",
    "\n",
    "__NTILE, CUME_DIST, PERCENT_RANK__\n",
    "\n",
    "```sql\n",
    "SELECT t.col_1, t.col_2 \n",
    ", CASE \n",
    "    WHEN t.n_buckets_by_col2 = 1 THEN 'cat_1'\n",
    "    WHEN t.n_buckets_by_col2 = 2 THEN 'cat_2'\n",
    "    ...\n",
    "    ELSE 'cat_n'\n",
    "END AS cat_col2 \n",
    "FROM (\n",
    "    SELECT *\n",
    "    , NTILE([N]) OVER (\n",
    "        PARTITION BY col_1\n",
    "        ORDER BY col_2 \n",
    "    ) AS n_buckets_by_col2\n",
    "    FROM tab_name \n",
    "    WHERE [CONDITION]\n",
    ") AS t; \n",
    "```\n",
    "\n",
    "\n",
    "Pick top N% of the data \n",
    "\n",
    "```sql\n",
    "SELECT t.col_1, t.col_2 \n",
    "FROM (\n",
    "    SELECT *\n",
    "    , CUM_DIST() OVER (\n",
    "        PARTITION BY col_1\n",
    "        ORDER BY col_2 DESC \n",
    "    ) cum_dist_col2_part1 \n",
    "    FROM tab_name \n",
    ") AS t \n",
    "WHERE t.cum_dist_col2_part1 <= [N]/100\n",
    "```\n",
    "\n",
    "\n",
    "What's the rank of col_1 = 'VAL_1'?\n",
    "\n",
    "```sql\n",
    "SELECT t.rank_col1_by_col2 \n",
    "FROM (\n",
    "    SELECT *\n",
    "    , PERCENT_RANK() OVER (\n",
    "        PARTITION BY col_1\n",
    "        ORDER BY col_2 DESC \n",
    "    ) AS rank_col1_by_col2 \n",
    "    FROM tab_name \n",
    ") AS t \n",
    "WHERE t.col_1 = 'VAL_1'; \n",
    "```\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f04c80da",
   "metadata": {},
   "source": [
    "# Recursive SQL Queries\n",
    "\n",
    "# PIVOT table / CROSSTAB function\n",
    "\n",
    "# Materialized Views\n",
    "\n",
    "# Stored Procedure\n",
    "\n",
    "# User Defined Functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "abfaccb6",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}