{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "7f0fc0f0",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    }
   ],
   "source": [
    "# enable sql\n",
    "!pip install ipython-sql >> /dev/null\n",
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "412a7b61",
   "metadata": {},
   "outputs": [],
   "source": [
    "# make a database, let's call it mollie\n",
    "%sql sqlite:///mollie.sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "dc56dd7c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import our mollie CSVs\n",
    "!sqlite3 mollie.sqlite3 -separator \",\" '.import ./customers.csv mollie_customers'\n",
    "!sqlite3 mollie.sqlite3 -separator \",\" '.import ./payments.csv mollie_payments'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "f59b6a11",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CREATE TABLE IF NOT EXISTS \"mollie_customers\"(\n",
      "  \"resource\" TEXT,\n",
      "  \"id\" TEXT,\n",
      "  \"mode\" TEXT,\n",
      "  \"name\" TEXT,\n",
      "  \"email\" TEXT,\n",
      "  \"locale\" TEXT,\n",
      "  \"metadata\" TEXT,\n",
      "  \"createdAt\" TEXT,\n",
      "  \"_links\" TEXT\n",
      ");\n",
      "CREATE TABLE IF NOT EXISTS \"mollie_payments\"(\n",
      "  \"resource\" TEXT,\n",
      "  \"id\" TEXT,\n",
      "  \"createdAt\" TEXT,\n",
      "  \"paidAt\" TEXT,\n",
      "  \"description\" TEXT,\n",
      "  \"method\" TEXT,\n",
      "  \"status\" TEXT,\n",
      "  \"isCancelable\" TEXT,\n",
      "  \"sequenceType\" TEXT,\n",
      "  \"profileId\" TEXT,\n",
      "  \"customerId\" TEXT,\n",
      "  \"mandateId\" TEXT,\n",
      "  \"details.consumerName\" TEXT,\n",
      "  \"details.consumerAccount\" TEXT,\n",
      "  \"details.consumerBic\" TEXT,\n",
      "  \"amount.value\" TEXT,\n",
      "  \"amount.currency\" TEXT,\n",
      "  \"settlementAmount.value\" TEXT,\n",
      "  \"settlementAmount.currency\" TEXT,\n",
      "  \"amountRefunded.value\" TEXT,\n",
      "  \"amountRefunded.currency\" TEXT,\n",
      "  \"amountRemaining.value\" TEXT,\n",
      "  \"amountRemaining.currency\" TEXT,\n",
      "  \"customer.resource\" TEXT,\n",
      "  \"customer.mode\" TEXT,\n",
      "  \"customer.name\" TEXT,\n",
      "  \"customer.email\" TEXT,\n",
      "  \"customer.locale\" TEXT,\n",
      "  \"customer.metadata\" TEXT,\n",
      "  \"customer.createdAt\" TEXT,\n",
      "  \"customer._links\" TEXT\n",
      ");\n"
     ]
    }
   ],
   "source": [
    "# let's check out their data structures\n",
    "!sqlite3 mollie.sqlite3 '.schema mollie_customers'\n",
    "!sqlite3 mollie.sqlite3 '.schema mollie_payments'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "5c11902f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   sqlite://\n",
      " * sqlite:///mollie.sqlite3\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>resource</th>\n",
       "        <th>id</th>\n",
       "        <th>mode</th>\n",
       "        <th>name</th>\n",
       "        <th>email</th>\n",
       "        <th>locale</th>\n",
       "        <th>metadata</th>\n",
       "        <th>createdAt</th>\n",
       "        <th>_links</th>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# preview the customers (sensitive data, so results censored from version control)\n",
    "%sql select * from mollie_customers limit 10;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "627724bf",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   sqlite://\n",
      " * sqlite:///mollie.sqlite3\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>resource</th>\n",
       "        <th>id</th>\n",
       "        <th>createdAt</th>\n",
       "        <th>paidAt</th>\n",
       "        <th>description</th>\n",
       "        <th>method</th>\n",
       "        <th>status</th>\n",
       "        <th>isCancelable</th>\n",
       "        <th>sequenceType</th>\n",
       "        <th>profileId</th>\n",
       "        <th>customerId</th>\n",
       "        <th>mandateId</th>\n",
       "        <th>details.consumerName</th>\n",
       "        <th>details.consumerAccount</th>\n",
       "        <th>details.consumerBic</th>\n",
       "        <th>amount.value</th>\n",
       "        <th>amount.currency</th>\n",
       "        <th>settlementAmount.value</th>\n",
       "        <th>settlementAmount.currency</th>\n",
       "        <th>amountRefunded.value</th>\n",
       "        <th>amountRefunded.currency</th>\n",
       "        <th>amountRemaining.value</th>\n",
       "        <th>amountRemaining.currency</th>\n",
       "        <th>customer.resource</th>\n",
       "        <th>customer.mode</th>\n",
       "        <th>customer.name</th>\n",
       "        <th>customer.email</th>\n",
       "        <th>customer.locale</th>\n",
       "        <th>customer.metadata</th>\n",
       "        <th>customer.createdAt</th>\n",
       "        <th>customer._links</th>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# preview the payments (same)\n",
    "%sql select * from mollie_payments limit 10;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "de2099ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "-- zoek naar mensen die het laatst hun maandelijkse contributie betaald hebben in september\n",
    "select *\n",
    "from mollie_customers join mollie_payments\n",
    "on mollie_customers.id = mollie_payments.customerId\n",
    "where mollie_payments.status = 'paid'\n",
    "and mollie_payments.paidAt > '2021-09-01'\n",
    "and mollie_payments.paidAt < '2021-10-01'\n",
    "and mollie_payments.description like 'Maandelijkse%'\n",
    "limit 50\n",
    ";\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "7d0f0070",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   sqlite://\n",
      " * sqlite:///mollie.sqlite3\n",
      "Done.\n",
      "Returning data to local variable leden_maandelijkse_contributie_laatst_betaald_in_september\n"
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "leden_maandelijkse_contributie_laatst_betaald_in_september <<\n",
    "-- zelfde query maar nou opslaan ipv preview\n",
    "select *\n",
    "from mollie_customers join mollie_payments\n",
    "on mollie_customers.id = mollie_payments.customerId\n",
    "where mollie_payments.status = 'paid'\n",
    "and mollie_payments.paidAt > '2021-09-01'\n",
    "and mollie_payments.paidAt < '2021-10-01'\n",
    "and mollie_payments.description like 'Maandelijkse%'\n",
    "-- limit 50\n",
    ";\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "61245263",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<a href=\"./files/./laatst_september.csv\">CSV results</a>"
      ],
      "text/plain": [
       "CSV results at /home/jovyan/work/Downloads/./laatst_september.csv"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "leden_maandelijkse_contributie_laatst_betaald_in_september.csv('./laatst_september.csv')\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}