SQL_queries.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Metrocar Funnel Exploration "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/1m/hvnyjbcx2rxfb9d__rb_m7h00000gp/T/ipykernel_17546/1537861751.py:5: DtypeWarning: Columns (4,7,8,9) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  ride_requests = pd.read_csv('metrocar_data/ride_requests.csv')\n"
     ]
    }
   ],
   "source": [
    "\n",
    "# import the  data\n",
    "\n",
    "app_downloads = pd.read_csv('metrocar_data/app_downloads.csv')\n",
    "reviews = pd.read_csv('metrocar_data/reviews.csv')\n",
    "ride_requests = pd.read_csv('metrocar_data/ride_requests.csv')\n",
    "signups = pd.read_csv('metrocar_data/signups.csv')\n",
    "transactions = pd.read_csv('metrocar_data/transactions.csv')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create the SQLite connection\n",
    "\n",
    "cnn = sqlite3.connect('cars.db')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "223652"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Save the data to SQLite\n",
    "\n",
    "app_downloads.to_sql('app_downloads', cnn, if_exists='replace')\n",
    "reviews.to_sql('reviews', cnn, if_exists='replace')\n",
    "ride_requests.to_sql('ride_requests', cnn, if_exists='replace')\n",
    "signups.to_sql('signups', cnn, if_exists='replace')\n",
    "transactions.to_sql('transactions', cnn, if_exists='replace')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: @cars.db'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load the ipython-sql extension\n",
    "%load_ext sql\n",
    "\n",
    "# Connect to the SQLite database\n",
    "%sql sqlite:///cars.db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///cars.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>COUNT(*)</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>23608</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(23608,)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT COUNT(*)\n",
    "FROM app_downloads;"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dataset structure\n",
    "\n",
    "Description of each table and its columns.\n",
    "\n",
    "#### app_downloads: contains information about app downloads\n",
    "* app_download_key: unique id of an app download\n",
    "* platform: ios, android or web\n",
    "* download_ts: download timestamp\n",
    "\n",
    "\n",
    "#### signups: contains information about new user signups\n",
    "\n",
    "* user_id: primary id for a user\n",
    "* session_id: id of app download\n",
    "* signup_ts: signup timestamp\n",
    "* age_range: the age range the user belongs to\n",
    "\n",
    "\n",
    "\n",
    "#### ride_requests: contains information about rides\n",
    "\n",
    "\n",
    "* ride_id: primary id for a ride\n",
    "* user_id: foreign key to user (requester)\n",
    "* driver_id: foreign key to driver\n",
    "* request_ts: ride request timestamp\n",
    "* accept_ts: driver accept timestamp\n",
    "* pickup_location: pickup coordinates\n",
    "* destination_location: destination coordinates\n",
    "* pickup_ts: pickup timestamp\n",
    "* dropoff_ts: dropoff timestamp\n",
    "* cancel_ts: ride cancel timestamp (accept, pickup and dropoff timestamps may be null)\n",
    "\n",
    "\n",
    "#### transactions: contains information about financial transactions based on completed rides:\n",
    "\n",
    "\n",
    "* ride_id: foreign key to ride\n",
    "* purchase_amount_usd: purchase amount in USD\n",
    "* charge_status: approved, cancelled\n",
    "* transaction_ts: transaction timestamp\n",
    "\n",
    "\n",
    "#### reviews: contains information about driver reviews once rides are completed\n",
    "\n",
    "\n",
    "* review_id: primary id of review\n",
    "* ride_id: foreign key to ride\n",
    "* driver_id: foreign key to driver\n",
    "* user_id: foreign key to user (requester)\n",
    "* rating: rating from 0 to 5\n",
    "* free_response: text response given by user/requester"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many times was the app downloaded?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///cars.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>AVG(avg_rating)</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>3.0628403863845386</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(3.0628403863845386,)]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH avg_ratings AS (SELECT user_id, ROUND(AVG(rating),2) avg_rating\n",
    "FROM reviews\n",
    "GROUP BY user_id)\n",
    "\n",
    "SELECT AVG(avg_rating)\n",
    "FROM avg_ratings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///cars.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Number_of_downloads</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>23608</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(23608,)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT COUNT(*) Number_of_downloads\n",
    "FROM app_downloads;"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many users signed up on the app?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///cars.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>total_user_signups</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>17623</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(17623,)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT COUNT (DISTINCT user_id) AS total_user_signups\n",
    "FROM signups"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many rides were requested through the app?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///cars.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>total_requests</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>385477</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(385477,)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT COUNT(request_ts) AS total_requests\n",
    "FROM ride_requests\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many unique users requested a ride through the Metrocar app?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///cars.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>total_unique_requests</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>12406</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(12406,)]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT COUNT(DISTINCT user_id) AS total_unique_requests\n",
    "FROM ride_requests\n",
    "WHERE request_ts > 0 "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many unique users completed a ride through the Metrocar app?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///cars.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>total_unique_rides</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>6233</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(6233,)]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT COUNT(DISTINCT user_id) AS total_unique_rides\n",
    "FROM ride_requests\n",
    "WHERE dropoff_ts > 0 "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many rides were requested and completed through the app?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///cars.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>total_accepts</th>\n",
       "            <th>total_completed_requests</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>248379</td>\n",
       "            <td>223652</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(248379, 223652)]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT COUNT(accept_ts) AS total_accepts, COUNT(dropoff_ts) AS total_completed_requests\n",
    "FROM ride_requests"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many rides were requested and how many unique users requested a ride?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///cars.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>total_requests</th>\n",
       "            <th>total_users</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>385477</td>\n",
       "            <td>12406</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(385477, 12406)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT COUNT(ride_id) total_requests, COUNT(DISTINCT user_id) total_users\n",
    "FROM ride_requests"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What is the average time of a ride from pick up to drop off?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "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>ride_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>driver_id</th>\n",
       "      <th>request_ts</th>\n",
       "      <th>accept_ts</th>\n",
       "      <th>pickup_location</th>\n",
       "      <th>dropoff_location</th>\n",
       "      <th>pickup_ts</th>\n",
       "      <th>dropoff_ts</th>\n",
       "      <th>cancel_ts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3080556</td>\n",
       "      <td>108995</td>\n",
       "      <td>116266.0</td>\n",
       "      <td>2021-07-26 09:01:00</td>\n",
       "      <td>2021-07-26 09:19:00</td>\n",
       "      <td>40.72216744 -73.96212375</td>\n",
       "      <td>40.72262277 -73.87869592</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-07-26 09:25:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3081967</td>\n",
       "      <td>110902</td>\n",
       "      <td>106286.0</td>\n",
       "      <td>2021-08-23 16:42:00</td>\n",
       "      <td>2021-08-23 16:59:00</td>\n",
       "      <td>40.74316496 -73.90019974</td>\n",
       "      <td>40.79460742 -73.81512246</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-08-23 17:09:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3088174</td>\n",
       "      <td>114998</td>\n",
       "      <td>116029.0</td>\n",
       "      <td>2021-11-13 17:06:00</td>\n",
       "      <td>2021-11-13 17:23:00</td>\n",
       "      <td>40.76639545 -73.877075</td>\n",
       "      <td>40.75548354 -73.9505886</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-11-13 17:33:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3180652</td>\n",
       "      <td>112421</td>\n",
       "      <td>109517.0</td>\n",
       "      <td>2021-10-16 15:28:00</td>\n",
       "      <td>2021-10-16 15:47:00</td>\n",
       "      <td>40.76499488 -73.8481648</td>\n",
       "      <td>40.86129334 -73.89895067</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-10-16 15:52:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3191244</td>\n",
       "      <td>104964</td>\n",
       "      <td>106628.0</td>\n",
       "      <td>2021-05-07 19:31:00</td>\n",
       "      <td>2021-05-07 19:51:00</td>\n",
       "      <td>40.73396733 -73.79521329</td>\n",
       "      <td>40.88348849 -74.03331643</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-05-07 19:58:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ride_id  user_id  driver_id           request_ts            accept_ts  \\\n",
       "0  3080556   108995   116266.0  2021-07-26 09:01:00  2021-07-26 09:19:00   \n",
       "1  3081967   110902   106286.0  2021-08-23 16:42:00  2021-08-23 16:59:00   \n",
       "2  3088174   114998   116029.0  2021-11-13 17:06:00  2021-11-13 17:23:00   \n",
       "3  3180652   112421   109517.0  2021-10-16 15:28:00  2021-10-16 15:47:00   \n",
       "4  3191244   104964   106628.0  2021-05-07 19:31:00  2021-05-07 19:51:00   \n",
       "\n",
       "            pickup_location          dropoff_location pickup_ts dropoff_ts  \\\n",
       "0  40.72216744 -73.96212375  40.72262277 -73.87869592       NaN        NaN   \n",
       "1  40.74316496 -73.90019974  40.79460742 -73.81512246       NaN        NaN   \n",
       "2    40.76639545 -73.877075   40.75548354 -73.9505886       NaN        NaN   \n",
       "3   40.76499488 -73.8481648  40.86129334 -73.89895067       NaN        NaN   \n",
       "4  40.73396733 -73.79521329  40.88348849 -74.03331643       NaN        NaN   \n",
       "\n",
       "             cancel_ts  \n",
       "0  2021-07-26 09:25:00  \n",
       "1  2021-08-23 17:09:00  \n",
       "2  2021-11-13 17:33:00  \n",
       "3  2021-10-16 15:52:00  \n",
       "4  2021-05-07 19:58:00  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ride_requests.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///cars.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>average_duration_mins</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>52.61</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(52.61,)]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT ROUND(AVG((strftime('%s', dropoff_ts) - strftime('%s', pickup_ts)) / 60),2) AS average_duration_mins\n",
    "FROM ride_requests"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many rides were accepted by a driver?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///cars.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>total_accepted_requests</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>248379</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(248379,)]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT COUNT(accept_ts) AS total_accepted_requests\n",
    "FROM ride_requests"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many rides did we successfully collect payments and how much was collected?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///cars.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>total_transations</th>\n",
       "            <th>total_usd_collected</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>212628</td>\n",
       "            <td>4251667.61</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(212628, 4251667.61)]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT COUNT(transaction_ts) AS total_transations, ROUND(SUM(purchase_amount_usd), 2) AS total_usd_collected\n",
    "FROM transactions\n",
    "WHERE charge_status LIKE 'approved'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///cars.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>COUNT(DISTINCT user_id)</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>6233</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(6233,)]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT COUNT(DISTINCT user_id)\n",
    "FROM transactions t\n",
    "LEFT JOIN ride_requests r\n",
    "ON r.ride_id = t.ride_id\n",
    "WHERE charge_status LIKE 'Approved'"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many ride requests happened on each platform?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///cars.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>platform</th>\n",
       "            <th>total_requests</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>android</td>\n",
       "            <td>112317</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>ios</td>\n",
       "            <td>234693</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>web</td>\n",
       "            <td>38467</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('android', 112317), ('ios', 234693), ('web', 38467)]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT platform, COUNT(request_ts) total_requests\n",
    "FROM app_downloads a\n",
    "LEFT JOIN signups s\n",
    "ON a.app_download_key = s.session_id\n",
    "LEFT JOIN ride_requests r\n",
    "ON r.user_id = s.user_id\n",
    "GROUP BY 1"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What is the drop-off from users signing up to users requesting a ride?\n",
    "\n",
    "Reference: https://popsql.com/sql-templates/marketing/running-a-funnel-analysis#calculating-drop-off-at-each-step"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///cars.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>step</th>\n",
       "            <th>count</th>\n",
       "            <th>previous_count</th>\n",
       "            <th>drop_off</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Ride Request</td>\n",
       "            <td>385477</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sign Up</td>\n",
       "            <td>17623</td>\n",
       "            <td>385477</td>\n",
       "            <td>0.95</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Ride Request', 385477, None, None), ('Sign Up', 17623, 385477, 0.95)]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "WITH steps AS (\n",
    "    SELECT 'Sign Up' as step, COUNT(DISTINCT user_id) as count FROM signups\n",
    "    UNION\n",
    "    SELECT 'Ride Request' as step, COUNT(request_ts) as count FROM ride_requests\n",
    ")\n",
    "\n",
    "\n",
    "SELECT step, \n",
    "        count, \n",
    "        lag(count, 1) OVER() as previous_count,\n",
    "        ROUND((1.0 - count * 1.0 / lag(count) OVER (ORDER BY step)), 2) AS drop_off\n",
    "\n",
    "from steps;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('2021-01-01 00:05:59', '2021-12-31 23:52:27')"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "app_downloads['download_ts'].min(), app_downloads['download_ts'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///cars.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>SUM(user_count)</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>23608</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(23608,)]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "WITH funnel AS (SELECT\n",
    "  'App Download' AS step,\n",
    "  DATE(d.download_ts) AS download_date,\n",
    "  d.platform,\n",
    "  s.age_range,\n",
    "  COUNT(download_ts) AS user_count,\n",
    "  NULL AS ride_count\n",
    "FROM app_downloads d\n",
    "LEFT JOIN signups s ON s.session_id = d.app_download_key\n",
    "GROUP BY download_date, d.platform, s.age_range\n",
    "\n",
    "UNION\n",
    "\n",
    "SELECT\n",
    "  'Signup' AS step,\n",
    "  DATE(d.download_ts) AS download_date,\n",
    "  d.platform,\n",
    "  s.age_range,\n",
    "  COUNT(DISTINCT s.user_id) AS user_count,\n",
    "  NULL AS ride_count\n",
    "FROM app_downloads d\n",
    "INNER JOIN signups s ON s.session_id = d.app_download_key\n",
    "GROUP BY download_date, d.platform, s.age_range\n",
    "\n",
    "UNION\n",
    "\n",
    "SELECT\n",
    "  'Ride Request' AS step,\n",
    "  DATE(d.download_ts) AS download_date,\n",
    "  d.platform,\n",
    "  s.age_range,\n",
    "  COUNT(DISTINCT r.user_id) AS user_count,\n",
    "  COUNT(DISTINCT r.ride_id) AS ride_count\n",
    "FROM app_downloads d\n",
    "INNER JOIN signups s ON s.session_id = d.app_download_key\n",
    "INNER JOIN ride_requests r ON r.user_id = s.user_id\n",
    "GROUP BY download_date, d.platform, s.age_range\n",
    "\n",
    "UNION\n",
    "\n",
    "SELECT\n",
    "  'Driver Acceptance' AS step,\n",
    "  DATE(d.download_ts) AS download_date,\n",
    "  d.platform,\n",
    "  s.age_range,\n",
    "  COUNT(DISTINCT r.user_id) AS user_count,\n",
    "  COUNT(DISTINCT r.ride_id) AS ride_count\n",
    "FROM app_downloads d\n",
    "INNER JOIN signups s ON s.session_id = d.app_download_key\n",
    "INNER JOIN ride_requests r ON r.user_id = s.user_id\n",
    "WHERE r.accept_ts IS NOT NULL\n",
    "GROUP BY download_date, d.platform, s.age_range\n",
    "\n",
    "UNION\n",
    "\n",
    "SELECT\n",
    "  'Completed Ride' AS step,\n",
    "  DATE(d.download_ts) AS download_date,\n",
    "  d.platform,\n",
    "  s.age_range,\n",
    "  COUNT(DISTINCT r.user_id) AS user_count,\n",
    "  COUNT(DISTINCT r.ride_id) AS ride_count\n",
    "FROM app_downloads d\n",
    "INNER JOIN signups s ON s.session_id = d.app_download_key\n",
    "INNER JOIN ride_requests r ON r.user_id = s.user_id\n",
    "INNER JOIN transactions t ON t.ride_id = r.ride_id\n",
    "WHERE r.cancel_ts IS NULL AND t.charge_status = 'Approved'\n",
    "GROUP BY download_date, d.platform, s.age_range\n",
    "\n",
    "UNION\n",
    "\n",
    "SELECT\n",
    "  'Completed Payment' AS step,\n",
    "  DATE(d.download_ts) AS download_date,\n",
    "  d.platform,\n",
    "  s.age_range,\n",
    "  COUNT(DISTINCT r.user_id) AS user_count,\n",
    "  COUNT(DISTINCT r.ride_id) AS ride_count\n",
    "FROM app_downloads d\n",
    "INNER JOIN signups s ON s.session_id = d.app_download_key\n",
    "INNER JOIN ride_requests r ON r.user_id = s.user_id\n",
    "INNER JOIN transactions t ON t.ride_id = r.ride_id\n",
    "WHERE r.cancel_ts IS NULL AND t.charge_status = 'Approved'\n",
    "GROUP BY download_date, d.platform, s.age_range\n",
    "\n",
    "UNION\n",
    "\n",
    "SELECT\n",
    "  'Completed Review' AS step,\n",
    "  DATE(d.download_ts) AS download_date,\n",
    "  d.platform,\n",
    "  s.age_range,\n",
    "  COUNT(DISTINCT r.user_id) AS user_count,\n",
    "  COUNT(DISTINCT r.ride_id) AS ride_count\n",
    "FROM app_downloads d\n",
    "INNER JOIN signups s ON s.session_id = d.app_download_key\n",
    "INNER JOIN ride_requests r ON r.user_id = s.user_id\n",
    "INNER JOIN transactions t ON t.ride_id = r.ride_id\n",
    "INNER JOIN reviews rev ON rev.ride_id = t.ride_id\n",
    "WHERE r.cancel_ts IS NULL AND t.charge_status = 'Approved' AND rev.rating IS NOT NULL\n",
    "GROUP BY download_date, d.platform, s.age_range\n",
    "ORDER BY step, platform, age_range, download_date)\n",
    "\n",
    "SELECT SUM(user_count)\n",
    "FROM funnel\n",
    "WHERE step LIKE 'App Download';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# aggregate the data to a user level and export as .csv for analysis in Tableau \n",
    "\n",
    "df = pd.read_sql_query(\"\"\"\n",
    "SELECT\n",
    "  'App Download' AS step,\n",
    "  DATE(d.download_ts) AS download_date,\n",
    "  d.platform,\n",
    "  s.age_range,\n",
    "  COUNT(DISTINCT download_ts) AS user_count,\n",
    "  NULL AS ride_count\n",
    "FROM app_downloads d\n",
    "LEFT JOIN signups s ON s.session_id = d.app_download_key\n",
    "GROUP BY download_date, d.platform, s.age_range\n",
    "\n",
    "UNION\n",
    "\n",
    "SELECT\n",
    "  'Signup' AS step,\n",
    "  DATE(d.download_ts) AS download_date,\n",
    "  d.platform,\n",
    "  s.age_range,\n",
    "  COUNT(DISTINCT s.user_id) AS user_count,\n",
    "  NULL AS ride_count\n",
    "FROM app_downloads d\n",
    "INNER JOIN signups s ON s.session_id = d.app_download_key\n",
    "GROUP BY download_date, d.platform, s.age_range\n",
    "\n",
    "UNION\n",
    "\n",
    "SELECT\n",
    "  'Ride Request' AS step,\n",
    "  DATE(d.download_ts) AS download_date,\n",
    "  d.platform,\n",
    "  s.age_range,\n",
    "  COUNT(DISTINCT r.user_id) AS user_count,\n",
    "  COUNT(DISTINCT r.ride_id) AS ride_count\n",
    "FROM app_downloads d\n",
    "INNER JOIN signups s ON s.session_id = d.app_download_key\n",
    "INNER JOIN ride_requests r ON r.user_id = s.user_id\n",
    "GROUP BY download_date, d.platform, s.age_range\n",
    "\n",
    "UNION\n",
    "\n",
    "SELECT\n",
    "  'Driver Acceptance' AS step,\n",
    "  DATE(d.download_ts) AS download_date,\n",
    "  d.platform,\n",
    "  s.age_range,\n",
    "  COUNT(DISTINCT r.user_id) AS user_count,\n",
    "  COUNT(DISTINCT r.ride_id) AS ride_count\n",
    "FROM app_downloads d\n",
    "INNER JOIN signups s ON s.session_id = d.app_download_key\n",
    "INNER JOIN ride_requests r ON r.user_id = s.user_id\n",
    "WHERE r.accept_ts IS NOT NULL\n",
    "GROUP BY download_date, d.platform, s.age_range\n",
    "\n",
    "UNION\n",
    "\n",
    "SELECT\n",
    "  'Completed Ride' AS step,\n",
    "  DATE(d.download_ts) AS download_date,\n",
    "  d.platform,\n",
    "  s.age_range,\n",
    "  COUNT(DISTINCT r.user_id) AS user_count,\n",
    "  COUNT(DISTINCT r.ride_id) AS ride_count\n",
    "FROM app_downloads d\n",
    "INNER JOIN signups s ON s.session_id = d.app_download_key\n",
    "INNER JOIN ride_requests r ON r.user_id = s.user_id\n",
    "INNER JOIN transactions t ON t.ride_id = r.ride_id\n",
    "WHERE r.cancel_ts IS NULL AND t.charge_status = 'Approved'\n",
    "GROUP BY download_date, d.platform, s.age_range\n",
    "\n",
    "UNION\n",
    "\n",
    "SELECT\n",
    "  'Completed Payment' AS step,\n",
    "  DATE(d.download_ts) AS download_date,\n",
    "  d.platform,\n",
    "  s.age_range,\n",
    "  COUNT(DISTINCT r.user_id) AS user_count,\n",
    "  COUNT(DISTINCT r.ride_id) AS ride_count\n",
    "FROM app_downloads d\n",
    "INNER JOIN signups s ON s.session_id = d.app_download_key\n",
    "INNER JOIN ride_requests r ON r.user_id = s.user_id\n",
    "INNER JOIN transactions t ON t.ride_id = r.ride_id\n",
    "WHERE r.cancel_ts IS NULL AND t.charge_status = 'Approved'\n",
    "GROUP BY download_date, d.platform, s.age_range\n",
    "\n",
    "UNION\n",
    "\n",
    "SELECT\n",
    "  'Completed Review' AS step,\n",
    "  DATE(d.download_ts) AS download_date,\n",
    "  d.platform,\n",
    "  s.age_range,\n",
    "  COUNT(DISTINCT r.user_id) AS user_count,\n",
    "  COUNT(DISTINCT r.ride_id) AS ride_count\n",
    "FROM app_downloads d\n",
    "INNER JOIN signups s ON s.session_id = d.app_download_key\n",
    "INNER JOIN ride_requests r ON r.user_id = s.user_id\n",
    "INNER JOIN transactions t ON t.ride_id = r.ride_id\n",
    "INNER JOIN reviews rev ON rev.ride_id = t.ride_id\n",
    "WHERE r.cancel_ts IS NULL AND t.charge_status = 'Approved' AND rev.rating IS NOT NULL\n",
    "GROUP BY download_date, d.platform, s.age_range\n",
    "ORDER BY step, platform, age_range, download_date\n",
    ";\"\"\", cnn)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(26901, 6)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.to_csv(\"metrocars_agg_dataset.csv\", index=False)\n",
    "\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///cars.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>step</th>\n",
       "            <th>count</th>\n",
       "            <th>previous_count</th>\n",
       "            <th>conversion</th>\n",
       "            <th>drop_off</th>\n",
       "            <th>percent_of_top</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>App Download</td>\n",
       "            <td>23608</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "            <td>100.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Signup</td>\n",
       "            <td>17623</td>\n",
       "            <td>23608</td>\n",
       "            <td>74.65</td>\n",
       "            <td>25.35</td>\n",
       "            <td>74.65</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Request Ride</td>\n",
       "            <td>12406</td>\n",
       "            <td>17623</td>\n",
       "            <td>70.4</td>\n",
       "            <td>29.6</td>\n",
       "            <td>52.55</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Driver Acceptance</td>\n",
       "            <td>12278</td>\n",
       "            <td>12406</td>\n",
       "            <td>98.97</td>\n",
       "            <td>1.03</td>\n",
       "            <td>52.01</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Completed Ride</td>\n",
       "            <td>6233</td>\n",
       "            <td>12278</td>\n",
       "            <td>50.77</td>\n",
       "            <td>49.23</td>\n",
       "            <td>26.4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Completed Payment</td>\n",
       "            <td>5962</td>\n",
       "            <td>6233</td>\n",
       "            <td>95.65</td>\n",
       "            <td>4.35</td>\n",
       "            <td>25.25</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Completed Review</td>\n",
       "            <td>4163</td>\n",
       "            <td>5962</td>\n",
       "            <td>69.83</td>\n",
       "            <td>30.17</td>\n",
       "            <td>17.63</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('App Download', 23608, None, None, None, 100.0),\n",
       " ('Signup', 17623, 23608, 74.65, 25.35, 74.65),\n",
       " ('Request Ride', 12406, 17623, 70.4, 29.6, 52.55),\n",
       " ('Driver Acceptance', 12278, 12406, 98.97, 1.03, 52.01),\n",
       " ('Completed Ride', 6233, 12278, 50.77, 49.23, 26.4),\n",
       " ('Completed Payment', 5962, 6233, 95.65, 4.35, 25.25),\n",
       " ('Completed Review', 4163, 5962, 69.83, 30.17, 17.63)]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "WITH downloads AS (\n",
    "  SELECT\n",
    "    app_download_key,\n",
    "    MIN(download_ts) AS min_time\n",
    "  FROM app_downloads\n",
    "  GROUP BY app_download_key\n",
    "),\n",
    "sign_ups AS (\n",
    "  SELECT DISTINCT\n",
    "    s.session_id,\n",
    "    s.user_id\n",
    "  FROM downloads d\n",
    "  INNER JOIN signups s ON s.session_id = d.app_download_key\n",
    "),\n",
    "requests AS (\n",
    "  SELECT DISTINCT\n",
    "    rr.user_id\n",
    "  FROM sign_ups s\n",
    "  INNER JOIN ride_requests rr ON s.user_id = rr.user_id\n",
    "),\n",
    "accepted_requests AS (\n",
    "  SELECT DISTINCT\n",
    "    rr.user_id\n",
    "  FROM requests r\n",
    "  INNER JOIN ride_requests rr ON r.user_id = rr.user_id\n",
    "  WHERE rr.accept_ts IS NOT NULL\n",
    "),\n",
    "completed_rides AS (\n",
    "  SELECT DISTINCT\n",
    "    rr.user_id,\n",
    "    rr.ride_id\n",
    "  FROM accepted_requests a\n",
    "  INNER JOIN ride_requests rr ON a.user_id = rr.user_id\n",
    "  WHERE rr.cancel_ts IS NULL\n",
    "  GROUP BY 1\n",
    "),\n",
    "completed_payments AS (\n",
    "  SELECT \n",
    "    t.ride_id, r.user_id,\n",
    "    COUNT(DISTINCT r.user_id) AS completed_transactions_count\n",
    "  FROM completed_rides r\n",
    "  INNER JOIN transactions t ON r.ride_id = t.ride_id\n",
    "  WHERE t.charge_status LIKE 'Approved'\n",
    "  GROUP BY t.ride_id\n",
    "),\n",
    "completed_reviews AS (\n",
    "  SELECT \n",
    "    p.ride_id\n",
    "  FROM completed_payments p\n",
    "  INNER JOIN reviews r ON p.ride_id = r.ride_id\n",
    "  WHERE r.rating IS NOT NULL\n",
    "),\n",
    "steps AS (\n",
    "  SELECT 'App Download' AS step, COUNT(*) AS count\n",
    "  FROM downloads\n",
    "  UNION\n",
    "  SELECT 'Signup' AS step, COUNT(*) AS count\n",
    "  FROM sign_ups\n",
    "  UNION\n",
    "  SELECT 'Request Ride' AS step, COUNT(*) AS count\n",
    "  FROM requests\n",
    "  UNION\n",
    "  SELECT 'Driver Acceptance' AS step, COUNT(*) AS count\n",
    "  FROM accepted_requests\n",
    "  UNION\n",
    "  SELECT 'Completed Ride' AS step, COUNT(*) AS count\n",
    "  FROM completed_rides\n",
    "  UNION\n",
    "  SELECT 'Completed Payment' AS step, COUNT(*) AS count\n",
    "  FROM completed_payments\n",
    "  UNION\n",
    "  SELECT 'Completed Review' AS step, COUNT(*) AS count\n",
    "  FROM completed_reviews\n",
    "  ORDER BY count DESC\n",
    "),\n",
    "previous_counts AS (\n",
    "  SELECT\n",
    "    step,\n",
    "    count,\n",
    "    LAG(count) OVER () AS previous_count\n",
    "\n",
    "  FROM steps\n",
    ")\n",
    "SELECT\n",
    "  step,\n",
    "  count,\n",
    "  previous_count,\n",
    "  ROUND((CAST(count AS REAL) / previous_count)*100, 2) AS conversion,\n",
    "  ROUND((1.0 - CAST(count AS REAL) / previous_count)*100, 2) AS drop_off,\n",
    "  ROUND((CAST(count AS REAL) / (SELECT Max(count) FROM steps)) * 100, 2) AS percent_of_top\n",
    "\n",
    "\n",
    "FROM previous_counts;\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "palooza",
   "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.3"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}