{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# SQL Window Functions Tutorial: OVER, PARTITION BY, and Ranking\n",
        "Practice notebook for the Xenors lesson: https://xenors.in/data-science/sql-window-functions-data-analysis/\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "import pandas as pd\n",
        "df = pd.read_csv('../datasets/sql-window-functions-data-analysis.csv')\n",
        "df.head()\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Baseline example\n",
        "SQL to run in SQLite/PostgreSQL after loading the CSV:\n",
        "```sql\nWITH ranked AS (\n  SELECT o.*,\n         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn\n  FROM orders o\n)\nSELECT * FROM ranked WHERE rn = 1;\n```\n",
        "Checkpoint: Run the cell and inspect the printed values before changing anything.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Confirm table grain\n",
        "SQL to run in SQLite/PostgreSQL after loading the CSV:\n",
        "```sql\nSELECT COUNT(*) AS rows, COUNT(DISTINCT customer_id) AS customers\nFROM practice_orders;\n```\n",
        "Checkpoint: rows | customers\n30   | 12\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Inspect duplicate keys\n",
        "SQL to run in SQLite/PostgreSQL after loading the CSV:\n",
        "```sql\nSELECT customer_id, COUNT(*) AS n\nFROM practice_orders\nGROUP BY customer_id\nHAVING COUNT(*) > 1\nORDER BY n DESC;\n```\n",
        "Checkpoint: Customers with more than one row are listed before any join or aggregation.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Validate a grouped result\n",
        "SQL to run in SQLite/PostgreSQL after loading the CSV:\n",
        "```sql\nSELECT region, COUNT(*) AS orders, ROUND(SUM(amount),2) AS revenue\nFROM practice_orders\nGROUP BY region\nORDER BY revenue DESC;\n```\n",
        "Checkpoint: region | orders | revenue\nWest   | ...    | ...\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Compare detail and aggregate totals\n",
        "SQL to run in SQLite/PostgreSQL after loading the CSV:\n",
        "```sql\nWITH x AS (\n  SELECT amount FROM practice_orders WHERE amount IS NOT NULL\n)\nSELECT ROUND(SUM(amount),2) AS detail_total,\n       ROUND((SELECT SUM(amount) FROM x),2) AS check_total\nFROM x;\n```\n",
        "Checkpoint: detail_total and check_total should match exactly.\n"
      ]
    }
  ],
  "metadata": {
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "name": "python",
      "version": "3.x"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 5
}