5_Views/1_View_Intro.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "d2301430",
   "metadata": {},
   "source": [
    "<a target=\"_blank\" href=\"https://colab.research.google.com/github/lukebarousse/Int_SQL_Data_Analytics_Course/blob/main/5_Views/1_View_Intro.ipynb\">\n",
    "  <img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/>\n",
    "</a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Views Intro"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Overview"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 🥅 Analysis Goals\n",
    "\n",
    "- **Daily Revenue**: Return the daily net revenue. \n",
    "- **Cohort Revenue:** Calculate daily net revenue for each cohort.  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 📘 Concepts Covered\n",
    "\n",
    "- Create views\n",
    "- Use views"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Connecting to &#x27;postgresql://postgres:***@localhost:5432/contoso_100k&#x27;</span>"
      ],
      "text/plain": [
       "Connecting to 'postgresql://postgres:***@localhost:5432/contoso_100k'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import sys\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "\n",
    "# If running in Google Colab, install PostgreSQL and restore the database\n",
    "if 'google.colab' in sys.modules:\n",
    "    # Install PostgreSQL\n",
    "    !sudo apt-get install postgresql -qq > /dev/null 2>&1\n",
    "\n",
    "    # Start PostgreSQL service (suppress output)\n",
    "    !sudo service postgresql start > /dev/null 2>&1\n",
    "\n",
    "    # Set password for the 'postgres' user to avoid authentication errors (suppress output)\n",
    "    !sudo -u postgres psql -c \"ALTER USER postgres WITH PASSWORD 'password';\" > /dev/null 2>&1\n",
    "\n",
    "    # Create the 'colab_db' database (suppress output)\n",
    "    !sudo -u postgres psql -c \"CREATE DATABASE contoso_100k;\" > /dev/null 2>&1\n",
    "\n",
    "    # Download the PostgreSQL .sql dump\n",
    "    !wget -q -O contoso_100k.sql https://github.com/lukebarousse/Int_SQL_Data_Analytics_Course/releases/download/v.0.0.0/contoso_100k.sql\n",
    "\n",
    "    # Restore the dump file into the PostgreSQL database (suppress output)\n",
    "    !sudo -u postgres psql contoso_100k < contoso_100k.sql > /dev/null 2>&1\n",
    "\n",
    "    # Shift libraries from ipython-sql to jupysql\n",
    "    !pip uninstall -y ipython-sql > /dev/null 2>&1\n",
    "    !pip install jupysql > /dev/null 2>&1\n",
    "\n",
    "# Load the sql extension for SQL magic\n",
    "%load_ext sql\n",
    "\n",
    "# Connect to the PostgreSQL database\n",
    "%sql postgresql://postgres:password@localhost:5432/contoso_100k\n",
    "\n",
    "# Enable automatic conversion of SQL results to pandas DataFrames\n",
    "%config SqlMagic.autopandas = True\n",
    "\n",
    "# Disable named parameters for SQL magic\n",
    "%config SqlMagic.named_parameters = \"disabled\"\n",
    "\n",
    "# Display pandas number to two decimal places\n",
    "pd.options.display.float_format = '{:.2f}'.format"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "## Views"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 📝 Notes\n",
    "\n",
    "`CREATE VIEW`\n",
    "\n",
    "- **Why Use Views in PostgreSQL?**  \n",
    "  - Simplifies complex queries by storing them as reusable, named objects.  \n",
    "  - Ensures consistency and readability when multiple queries rely on the same logic.  \n",
    "  - Enhances security by restricting access to specific rows/columns.  \n",
    "  - Improves maintainability by centralizing changes to the query logic.\n",
    "\n",
    "- **Syntax:**  \n",
    "    ```sql\n",
    "    CREATE VIEW view_name AS\n",
    "    SELECT\n",
    "        column1,\n",
    "        column2,\n",
    "        column3\n",
    "    FROM table_name\n",
    "    ```\n",
    "    - `CREATE VIEW view_name AS`: Creates a new view with the specified name.\n",
    "    - `SELECT`: Defines the query whose results will be stored in the view.\n",
    "  \n",
    "### 🔑 Key Concepts\n",
    "- **📊 Business Terms**: \n",
    "  - Cohort Revenue: Total sales generated by a group of customers who started at the same time\n",
    "  - Daily Revenue Trends: Patterns in revenue performance over daily periods\n",
    "- **💡 Why It Matters**: Enables efficient analysis of cohort performance\n",
    "    - Simplifies complex revenue calculations for repeated use\n",
    "    - Maintains consistency in cohort analysis across queries\n",
    "    - Provides standardized way to track daily revenue patterns\n",
    "    - Provides insights into overall customer value trends and short-term customer activity for cohorts.\n",
    "- **🎯 Common Use Cases**: \n",
    "  - Cohort revenue analysis\n",
    "  - Daily trend tracking\n",
    "  - Revenue pattern identification\n",
    "- **📈 Related KPIs**: \n",
    "  - Daily revenue\n",
    "  - Cohort growth metrics\n",
    "\n",
    "### 📈 Analysis\n",
    "\n",
    "- Calculate the daily net revenue.\n",
    "- Find the total net revenue for all the cohorts. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Daily Net Revenue\n",
    "\n",
    "**`CREATE VIEWS`**\n",
    "\n",
    "1. Get the daily net revenue and number of orders for each customer.\n",
    "   - Select `orderdate`.\n",
    "   - Use `GROUP BY` to group by `orderdate`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;postgresql://postgres:***@localhost:5432/contoso_100k&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'postgresql://postgres:***@localhost:5432/contoso_100k'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "CREATE VIEW daily_revenue AS\n",
    "SELECT \n",
    "    orderdate,\n",
    "    SUM(quantity * netprice * exchangerate) AS total_revenue\n",
    "FROM sales\n",
    "GROUP BY \n",
    "    orderdate"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- **View the View in DBeaver:**\n",
    "    1. Click the `Views` folder\n",
    "    2. Refresh the `Views` using `F5`.\n",
    "    3. Then go to the left side in the Database Navigator. \n",
    "    4. Double click the view you created named `cohort_analysis`\n",
    "    5. Then go to the `Data` tab (if it doesn't go there by default).\n",
    "\n",
    "<img src=\"../Resources/images/5.1_dbeaver_views.gif\" alt=\"DBeaver Query Results\" width=\"50%\">\n",
    "\n",
    "- **View the View in Query:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;postgresql://postgres:***@localhost:5432/contoso_100k&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'postgresql://postgres:***@localhost:5432/contoso_100k'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">3294 rows affected.</span>"
      ],
      "text/plain": [
       "3294 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>orderdate</th>\n",
       "      <th>total_revenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-10-23</td>\n",
       "      <td>74893.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-04-24</td>\n",
       "      <td>43321.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-12-28</td>\n",
       "      <td>101464.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015-01-19</td>\n",
       "      <td>12002.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-02-12</td>\n",
       "      <td>156723.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3289</th>\n",
       "      <td>2023-10-19</td>\n",
       "      <td>114969.37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3290</th>\n",
       "      <td>2017-10-29</td>\n",
       "      <td>649.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3291</th>\n",
       "      <td>2023-02-08</td>\n",
       "      <td>158675.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3292</th>\n",
       "      <td>2021-08-03</td>\n",
       "      <td>47364.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3293</th>\n",
       "      <td>2017-07-01</td>\n",
       "      <td>72722.45</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3294 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       orderdate  total_revenue\n",
       "0     2017-10-23       74893.02\n",
       "1     2023-04-24       43321.33\n",
       "2     2017-12-28      101464.75\n",
       "3     2015-01-19       12002.09\n",
       "4     2019-02-12      156723.97\n",
       "...          ...            ...\n",
       "3289  2023-10-19      114969.37\n",
       "3290  2017-10-29         649.59\n",
       "3291  2023-02-08      158675.41\n",
       "3292  2021-08-03       47364.43\n",
       "3293  2017-07-01       72722.45\n",
       "\n",
       "[3294 rows x 2 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT *\n",
    "FROM daily_revenue"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- **Remove the View:**   \n",
    "**⛔️ WARNING: This is permanent!**\n",
    "    - **Delete from DBeaver:**\n",
    "    1. Click the `Views` folder\n",
    "    2. Refresh the `Views` using `F5`.\n",
    "    3. Right click the view you created named `daily_revenue`\n",
    "    4. Click `Delete` and confirm.\n",
    "        - Note: Select \"Cascade Delete\" if you also want to delete all other views that depend on this view.\n",
    "\n",
    "    - **Delete with SQL Query:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;postgresql://postgres:***@localhost:5432/contoso_100k&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'postgresql://postgres:***@localhost:5432/contoso_100k'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "DROP VIEW daily_revenue;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Create Project View\n",
    "\n",
    "**`CREATE VIEWS`**\n",
    "\n",
    "1. Get the daily net revenue and number of orders for each customer.\n",
    "   - Select `customerkey`, `orderdate`, and `total_net_revenue`\n",
    "   - Use `GROUP BY` to group by `customerkey` and `orderdate`.\n",
    "   - Use `SUM` to calculate the total net revenue for each customer per day.\n",
    "   - Use `COUNT` to calculate the number of orders for each customer per day."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;postgresql://postgres:***@localhost:5432/contoso_100k&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'postgresql://postgres:***@localhost:5432/contoso_100k'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">83099 rows affected.</span>"
      ],
      "text/plain": [
       "83099 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customerkey</th>\n",
       "      <th>orderdate</th>\n",
       "      <th>total_net_revenue</th>\n",
       "      <th>num_orders</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1506769</td>\n",
       "      <td>2022-03-04</td>\n",
       "      <td>996.79</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>909157</td>\n",
       "      <td>2021-11-16</td>\n",
       "      <td>1565.80</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2047462</td>\n",
       "      <td>2020-12-09</td>\n",
       "      <td>34.06</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1933480</td>\n",
       "      <td>2021-11-10</td>\n",
       "      <td>45.90</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1701958</td>\n",
       "      <td>2017-10-05</td>\n",
       "      <td>5144.64</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83094</th>\n",
       "      <td>1273185</td>\n",
       "      <td>2023-09-16</td>\n",
       "      <td>3452.05</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83095</th>\n",
       "      <td>420797</td>\n",
       "      <td>2022-12-29</td>\n",
       "      <td>278.90</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83096</th>\n",
       "      <td>642485</td>\n",
       "      <td>2023-03-08</td>\n",
       "      <td>574.31</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83097</th>\n",
       "      <td>863441</td>\n",
       "      <td>2023-03-23</td>\n",
       "      <td>475.84</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83098</th>\n",
       "      <td>389153</td>\n",
       "      <td>2023-11-28</td>\n",
       "      <td>19496.16</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>83099 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       customerkey   orderdate  total_net_revenue  num_orders\n",
       "0          1506769  2022-03-04             996.79           2\n",
       "1           909157  2021-11-16            1565.80           2\n",
       "2          2047462  2020-12-09              34.06           1\n",
       "3          1933480  2021-11-10              45.90           2\n",
       "4          1701958  2017-10-05            5144.64           1\n",
       "...            ...         ...                ...         ...\n",
       "83094      1273185  2023-09-16            3452.05           3\n",
       "83095       420797  2022-12-29             278.90           1\n",
       "83096       642485  2023-03-08             574.31           3\n",
       "83097       863441  2023-03-23             475.84           3\n",
       "83098       389153  2023-11-28           19496.16           6\n",
       "\n",
       "[83099 rows x 4 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT \n",
    "    s.customerkey,\n",
    "    s.orderdate,\n",
    "    SUM(s.quantity * s.netprice * s.exchangerate) AS total_net_revenue,\n",
    "    COUNT(s.orderkey) AS num_orders\n",
    "FROM sales s\n",
    "GROUP BY \n",
    "    s.customerkey,\n",
    "    s.orderdate\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2. Join with customer table to add demographic data:\n",
    "   - Use `LEFT JOIN` with `customer` table on `customerkey` to preserve all sales records\n",
    "   - Add customer columns:\n",
    "     - `countryfull`: Customer's country of residence\n",
    "     - `age`: Customer's age\n",
    "     - `givenname`: Customer's first name\n",
    "     - `surname`: Customer's last name\n",
    "   - Keep existing aggregations:\n",
    "     - `SUM()` for total revenue\n",
    "     - `COUNT()` for number of orders\n",
    "   - Group by all non-aggregated columns\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;postgresql://postgres:***@localhost:5432/contoso_100k&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'postgresql://postgres:***@localhost:5432/contoso_100k'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">83099 rows affected.</span>"
      ],
      "text/plain": [
       "83099 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customerkey</th>\n",
       "      <th>orderdate</th>\n",
       "      <th>total_net_revenue</th>\n",
       "      <th>count</th>\n",
       "      <th>countryfull</th>\n",
       "      <th>age</th>\n",
       "      <th>givenname</th>\n",
       "      <th>surname</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>15</td>\n",
       "      <td>2021-03-08</td>\n",
       "      <td>2217.41</td>\n",
       "      <td>1</td>\n",
       "      <td>Australia</td>\n",
       "      <td>55</td>\n",
       "      <td>Julian</td>\n",
       "      <td>McGuigan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>180</td>\n",
       "      <td>2018-07-28</td>\n",
       "      <td>525.31</td>\n",
       "      <td>1</td>\n",
       "      <td>Australia</td>\n",
       "      <td>65</td>\n",
       "      <td>Gabriel</td>\n",
       "      <td>Bosanquet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>180</td>\n",
       "      <td>2023-08-28</td>\n",
       "      <td>1984.90</td>\n",
       "      <td>2</td>\n",
       "      <td>Australia</td>\n",
       "      <td>65</td>\n",
       "      <td>Gabriel</td>\n",
       "      <td>Bosanquet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>185</td>\n",
       "      <td>2019-06-01</td>\n",
       "      <td>1395.52</td>\n",
       "      <td>1</td>\n",
       "      <td>Australia</td>\n",
       "      <td>40</td>\n",
       "      <td>Gabrielle</td>\n",
       "      <td>Castella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>243</td>\n",
       "      <td>2016-05-19</td>\n",
       "      <td>287.67</td>\n",
       "      <td>1</td>\n",
       "      <td>Australia</td>\n",
       "      <td>66</td>\n",
       "      <td>Maya</td>\n",
       "      <td>Atherton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83094</th>\n",
       "      <td>2099697</td>\n",
       "      <td>2022-09-13</td>\n",
       "      <td>38.20</td>\n",
       "      <td>3</td>\n",
       "      <td>United States</td>\n",
       "      <td>54</td>\n",
       "      <td>Phillipp</td>\n",
       "      <td>Maier</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83095</th>\n",
       "      <td>2099711</td>\n",
       "      <td>2016-08-13</td>\n",
       "      <td>2067.75</td>\n",
       "      <td>1</td>\n",
       "      <td>United States</td>\n",
       "      <td>80</td>\n",
       "      <td>Katerina</td>\n",
       "      <td>Pavlícková</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83096</th>\n",
       "      <td>2099711</td>\n",
       "      <td>2017-08-14</td>\n",
       "      <td>3940.92</td>\n",
       "      <td>1</td>\n",
       "      <td>United States</td>\n",
       "      <td>80</td>\n",
       "      <td>Katerina</td>\n",
       "      <td>Pavlícková</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83097</th>\n",
       "      <td>2099743</td>\n",
       "      <td>2022-03-17</td>\n",
       "      <td>469.62</td>\n",
       "      <td>2</td>\n",
       "      <td>United States</td>\n",
       "      <td>21</td>\n",
       "      <td>Luciana</td>\n",
       "      <td>Almonte</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83098</th>\n",
       "      <td>2099743</td>\n",
       "      <td>2023-02-11</td>\n",
       "      <td>598.46</td>\n",
       "      <td>1</td>\n",
       "      <td>United States</td>\n",
       "      <td>21</td>\n",
       "      <td>Luciana</td>\n",
       "      <td>Almonte</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>83099 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       customerkey   orderdate  total_net_revenue  count    countryfull  age  \\\n",
       "0               15  2021-03-08            2217.41      1      Australia   55   \n",
       "1              180  2018-07-28             525.31      1      Australia   65   \n",
       "2              180  2023-08-28            1984.90      2      Australia   65   \n",
       "3              185  2019-06-01            1395.52      1      Australia   40   \n",
       "4              243  2016-05-19             287.67      1      Australia   66   \n",
       "...            ...         ...                ...    ...            ...  ...   \n",
       "83094      2099697  2022-09-13              38.20      3  United States   54   \n",
       "83095      2099711  2016-08-13            2067.75      1  United States   80   \n",
       "83096      2099711  2017-08-14            3940.92      1  United States   80   \n",
       "83097      2099743  2022-03-17             469.62      2  United States   21   \n",
       "83098      2099743  2023-02-11             598.46      1  United States   21   \n",
       "\n",
       "       givenname     surname  \n",
       "0         Julian    McGuigan  \n",
       "1        Gabriel   Bosanquet  \n",
       "2        Gabriel   Bosanquet  \n",
       "3      Gabrielle    Castella  \n",
       "4           Maya    Atherton  \n",
       "...          ...         ...  \n",
       "83094   Phillipp       Maier  \n",
       "83095   Katerina  Pavlícková  \n",
       "83096   Katerina  Pavlícková  \n",
       "83097    Luciana     Almonte  \n",
       "83098    Luciana     Almonte  \n",
       "\n",
       "[83099 rows x 8 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    s.customerkey,\n",
    "    s.orderdate,\n",
    "    SUM(s.quantity * s.netprice * s.exchangerate) AS total_net_revenue,\n",
    "    COUNT(s.orderkey),\n",
    "    c.countryfull,\n",
    "    c.age,\n",
    "    c.givenname,\n",
    "    c.surname\n",
    "FROM sales s \n",
    "LEFT JOIN customer c ON c.customerkey = s.customerkey\n",
    "GROUP BY\n",
    "    s.customerkey,\n",
    "    s.orderdate,\n",
    "    c.countryfull,\n",
    "    c.age,\n",
    "    c.givenname,\n",
    "    c.surname\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3. Create a query that:\n",
    "   - Uses previous query as a CTE named `customer_revenue`\n",
    "   - Adds customer demographic data by joining with `customer` table\n",
    "   - Calculates first_purchase_date and cohort_year using window functions:\n",
    "       - `MIN(orderdate) OVER (PARTITION BY customerkey)` gets earliest purchase date per customer\n",
    "       - `EXTRACT(YEAR FROM MIN(orderdate) OVER (PARTITION BY customerkey))` gets cohort year\n",
    "   - Returns customer purchase data with demographics and cohort \n",
    "   \n",
    "> **Why didn't we include window functions in original query vice CTE?**  \n",
    "> Window functions are processed after GROUP BY: Window functions operate on the result set after aggregation (GROUP BY) has been applied.\n",
    "> Technically we could have used a window function in the original query, because we aggregated on the `orderdate` and thus all distinct `orderdate`s would have been maintained producing the correct results.\n",
    "> However, using window functions with group bys in my opinion is not a great practice as:\n",
    "> 1. If the order of operations aren't understood, window functions may yield unexpected results.\n",
    "> 2. The CTE approach provides better readability for the query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;postgresql://postgres:***@localhost:5432/contoso_100k&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'postgresql://postgres:***@localhost:5432/contoso_100k'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">83099 rows affected.</span>"
      ],
      "text/plain": [
       "83099 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customerkey</th>\n",
       "      <th>orderdate</th>\n",
       "      <th>total_net_revenue</th>\n",
       "      <th>count</th>\n",
       "      <th>countryfull</th>\n",
       "      <th>age</th>\n",
       "      <th>givenname</th>\n",
       "      <th>surname</th>\n",
       "      <th>first_purchase_date</th>\n",
       "      <th>cohort_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>15</td>\n",
       "      <td>2021-03-08</td>\n",
       "      <td>2217.41</td>\n",
       "      <td>1</td>\n",
       "      <td>Australia</td>\n",
       "      <td>55</td>\n",
       "      <td>Julian</td>\n",
       "      <td>McGuigan</td>\n",
       "      <td>2021-03-08</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>180</td>\n",
       "      <td>2018-07-28</td>\n",
       "      <td>525.31</td>\n",
       "      <td>1</td>\n",
       "      <td>Australia</td>\n",
       "      <td>65</td>\n",
       "      <td>Gabriel</td>\n",
       "      <td>Bosanquet</td>\n",
       "      <td>2018-07-28</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>180</td>\n",
       "      <td>2023-08-28</td>\n",
       "      <td>1984.90</td>\n",
       "      <td>2</td>\n",
       "      <td>Australia</td>\n",
       "      <td>65</td>\n",
       "      <td>Gabriel</td>\n",
       "      <td>Bosanquet</td>\n",
       "      <td>2018-07-28</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>185</td>\n",
       "      <td>2019-06-01</td>\n",
       "      <td>1395.52</td>\n",
       "      <td>1</td>\n",
       "      <td>Australia</td>\n",
       "      <td>40</td>\n",
       "      <td>Gabrielle</td>\n",
       "      <td>Castella</td>\n",
       "      <td>2019-06-01</td>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>243</td>\n",
       "      <td>2016-05-19</td>\n",
       "      <td>287.67</td>\n",
       "      <td>1</td>\n",
       "      <td>Australia</td>\n",
       "      <td>66</td>\n",
       "      <td>Maya</td>\n",
       "      <td>Atherton</td>\n",
       "      <td>2016-05-19</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83094</th>\n",
       "      <td>2099697</td>\n",
       "      <td>2022-09-13</td>\n",
       "      <td>38.20</td>\n",
       "      <td>3</td>\n",
       "      <td>United States</td>\n",
       "      <td>54</td>\n",
       "      <td>Phillipp</td>\n",
       "      <td>Maier</td>\n",
       "      <td>2022-09-13</td>\n",
       "      <td>2022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83095</th>\n",
       "      <td>2099711</td>\n",
       "      <td>2016-08-13</td>\n",
       "      <td>2067.75</td>\n",
       "      <td>1</td>\n",
       "      <td>United States</td>\n",
       "      <td>80</td>\n",
       "      <td>Katerina</td>\n",
       "      <td>Pavlícková</td>\n",
       "      <td>2016-08-13</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83096</th>\n",
       "      <td>2099711</td>\n",
       "      <td>2017-08-14</td>\n",
       "      <td>3940.92</td>\n",
       "      <td>1</td>\n",
       "      <td>United States</td>\n",
       "      <td>80</td>\n",
       "      <td>Katerina</td>\n",
       "      <td>Pavlícková</td>\n",
       "      <td>2016-08-13</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83097</th>\n",
       "      <td>2099743</td>\n",
       "      <td>2022-03-17</td>\n",
       "      <td>469.62</td>\n",
       "      <td>2</td>\n",
       "      <td>United States</td>\n",
       "      <td>21</td>\n",
       "      <td>Luciana</td>\n",
       "      <td>Almonte</td>\n",
       "      <td>2022-03-17</td>\n",
       "      <td>2022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83098</th>\n",
       "      <td>2099743</td>\n",
       "      <td>2023-02-11</td>\n",
       "      <td>598.46</td>\n",
       "      <td>1</td>\n",
       "      <td>United States</td>\n",
       "      <td>21</td>\n",
       "      <td>Luciana</td>\n",
       "      <td>Almonte</td>\n",
       "      <td>2022-03-17</td>\n",
       "      <td>2022</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>83099 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       customerkey   orderdate  total_net_revenue  count    countryfull  age  \\\n",
       "0               15  2021-03-08            2217.41      1      Australia   55   \n",
       "1              180  2018-07-28             525.31      1      Australia   65   \n",
       "2              180  2023-08-28            1984.90      2      Australia   65   \n",
       "3              185  2019-06-01            1395.52      1      Australia   40   \n",
       "4              243  2016-05-19             287.67      1      Australia   66   \n",
       "...            ...         ...                ...    ...            ...  ...   \n",
       "83094      2099697  2022-09-13              38.20      3  United States   54   \n",
       "83095      2099711  2016-08-13            2067.75      1  United States   80   \n",
       "83096      2099711  2017-08-14            3940.92      1  United States   80   \n",
       "83097      2099743  2022-03-17             469.62      2  United States   21   \n",
       "83098      2099743  2023-02-11             598.46      1  United States   21   \n",
       "\n",
       "       givenname     surname first_purchase_date cohort_year  \n",
       "0         Julian    McGuigan          2021-03-08        2021  \n",
       "1        Gabriel   Bosanquet          2018-07-28        2018  \n",
       "2        Gabriel   Bosanquet          2018-07-28        2018  \n",
       "3      Gabrielle    Castella          2019-06-01        2019  \n",
       "4           Maya    Atherton          2016-05-19        2016  \n",
       "...          ...         ...                 ...         ...  \n",
       "83094   Phillipp       Maier          2022-09-13        2022  \n",
       "83095   Katerina  Pavlícková          2016-08-13        2016  \n",
       "83096   Katerina  Pavlícková          2016-08-13        2016  \n",
       "83097    Luciana     Almonte          2022-03-17        2022  \n",
       "83098    Luciana     Almonte          2022-03-17        2022  \n",
       "\n",
       "[83099 rows x 10 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "WITH customer_revenue AS (\n",
    "\tSELECT\n",
    "\t\ts.customerkey,\n",
    "\t\ts.orderdate,\n",
    "\t\tSUM(s.quantity * s.netprice * s.exchangerate) AS total_net_revenue,\n",
    "\t\tCOUNT(s.orderkey),\n",
    "\t\tc.countryfull,\n",
    "\t\tc.age,\n",
    "\t\tc.givenname,\n",
    "\t\tc.surname\n",
    "\tFROM sales s \n",
    "\tLEFT JOIN customer c ON c.customerkey = s.customerkey\n",
    "\tGROUP BY\n",
    "\t\ts.customerkey,\n",
    "\t\ts.orderdate,\n",
    "\t\tc.countryfull,\n",
    "\t\tc.age,\n",
    "\t\tc.givenname,\n",
    "\t\tc.surname\n",
    ")\n",
    "SELECT\n",
    "\tcr.*,\n",
    "\tMIN(cr.orderdate) OVER (PARTITION BY cr.customerkey) AS first_purchase_date,\n",
    "\tEXTRACT(YEAR FROM MIN(cr.orderdate) OVER (PARTITION BY cr.customerkey)) AS cohort_year\n",
    "FROM customer_revenue cr \n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "4. Create a view in DBeaver using `CREATE VIEW`.  \n",
    "    - NOTE: This adds CREATE OR REPLACE VIEW to the query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;postgresql://postgres:***@localhost:5432/contoso_100k&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'postgresql://postgres:***@localhost:5432/contoso_100k'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "-- DROP VIEW IF EXISTS cohort_analysis;\n",
    "\n",
    "CREATE OR REPLACE VIEW cohort_analysis AS  --create view as cohort_analysis\n",
    "WITH customer_revenue AS (\n",
    "\tSELECT\n",
    "\t\ts.customerkey,\n",
    "\t\ts.orderdate,\n",
    "\t\tSUM(s.quantity * s.netprice * s.exchangerate) AS total_net_revenue,\n",
    "\t\tCOUNT(s.orderkey) AS num_orders,\n",
    "\t\tc.countryfull,\n",
    "\t\tc.age,\n",
    "\t\tc.givenname,\n",
    "\t\tc.surname\n",
    "\tFROM sales s \n",
    "\tLEFT JOIN customer c ON c.customerkey = s.customerkey\n",
    "\tGROUP BY\n",
    "\t\ts.customerkey,\n",
    "\t\ts.orderdate,\n",
    "\t\tc.countryfull,\n",
    "\t\tc.age,\n",
    "\t\tc.givenname,\n",
    "\t\tc.surname\n",
    ")\n",
    "SELECT\n",
    "\tcr.*,\n",
    "\tMIN(cr.orderdate) OVER (PARTITION BY cr.customerkey) AS first_purchase_date,\n",
    "\tEXTRACT(YEAR FROM MIN(cr.orderdate) OVER (PARTITION BY cr.customerkey)) AS cohort_year\n",
    "FROM customer_revenue cr "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "dbeaver output:\n",
    "\n",
    "![Create View](../Resources/images/5.1_dbeaver_create_view.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "5. Use the view and calculate the total net revenue.  \n",
    "\n",
    "   - Query the `cohort_analysis` view to retrieve `cohort_year` and `total_net_revenue`.  \n",
    "   - 🔔 Use `SUM(total_net_revenue)` to calculate the total revenue for all customers within each cohort for a specific day.  \n",
    "   - 🔔 Group by `cohort_year` to ensure the total revenue is aggregated at the cohort level.  \n",
    "   - 🔔 Select `cohort_year` and `total_revenue` for the final output.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;postgresql://postgres:***@localhost:5432/contoso_100k&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'postgresql://postgres:***@localhost:5432/contoso_100k'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">10 rows affected.</span>"
      ],
      "text/plain": [
       "10 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cohort_year</th>\n",
       "      <th>total_revenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015</td>\n",
       "      <td>14892230.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016</td>\n",
       "      <td>18360521.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017</td>\n",
       "      <td>21979733.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018</td>\n",
       "      <td>36460385.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019</td>\n",
       "      <td>36696243.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2020</td>\n",
       "      <td>11921900.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2021</td>\n",
       "      <td>18387736.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2022</td>\n",
       "      <td>29872808.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2023</td>\n",
       "      <td>14979328.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2024</td>\n",
       "      <td>2856649.33</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  cohort_year  total_revenue\n",
       "0        2015    14892230.47\n",
       "1        2016    18360521.74\n",
       "2        2017    21979733.96\n",
       "3        2018    36460385.42\n",
       "4        2019    36696243.88\n",
       "5        2020    11921900.97\n",
       "6        2021    18387736.18\n",
       "7        2022    29872808.30\n",
       "8        2023    14979328.33\n",
       "9        2024     2856649.33"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "    cohort_year,\n",
    "    SUM(total_net_revenue) AS total_revenue\n",
    "FROM cohort_analysis\n",
    "GROUP BY \n",
    "    cohort_year\n",
    "ORDER BY cohort_year;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"../Resources/images/5.1_cohort_rev.png\" alt=\"Cohort Revenue\" width=\"50%\">"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "sql_course",
   "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.11.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}