CaitlinRuble_JCrew_AnalyticsExcercises(2).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Take Home Analytics Excercises\n",
    "### Caitlin Ruble\n",
    "\n",
    "\"This is an exercise to gauge your familiarity with coding and think through analysis implications. You'll find a prompt and two columns below: 'Coding' and 'Questions'. For the 'Coding' portion please submit the SQL query you would run to figure out the answer. The tables are spelled out in the subsequent tabs and are populated with both a definition of the table/columns and SAMPLE data. The 'Questions' portion asks you to think through the implications of a given result.\n",
    "\n",
    "Feel free to submit in whatever format you're most comfortable with.\"\n",
    "\n",
    "\n",
    "### **Prompt** : You work for J.Crew. J.Crew has just acquired Madewell and you're working with a team that is trying to understand the Madewell business and opportunities to cross-sell to loyalty members."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### My approach: \n",
    "I'm going to use pandas and pandasql to load the sample databases and ensure my queries are working. I will write the MySQL versions of the queries as commented-out code cells within this notebook, and I will also save these to an .sql file to be saved in the same repository. These should be considered my final answers, though the SQLite queries will show my experimentation process and the results of running these queries with the provided sample data. Please note that sqlite has some subtle syntax differences from MySQL, which I have done my best to account for.\n",
    "\n",
    "First I'll need to load the appropriate libraries and set the provided example database tables up as DataFrames. Because they're so small and on Excel sheets that also contain text, I'll simply manually create the DataFrames."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Loading"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import pandasql as ps\n",
    "import datetime as dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "checkouts:\n",
      "   customer_id  cart_id       date\n",
      "0            1       56 2021-01-02\n",
      "1            2       57 2021-01-02\n",
      "2            3       58 2021-01-08\n",
      "3            4       59 2021-02-01\n",
      "4            1       60 2021-02-15\n",
      "5            2       61 2021-02-05\n",
      "6            3       62 2021-02-28\n",
      "7            4       63 2021-03-08\n",
      "checkout_items:\n",
      "    cart_id                 item  quantity  price_per_unit_cents\n",
      "0        56          White Shirt         4                  1200\n",
      "1        56           Mens Short         1                   600\n",
      "2        56  Cotton jersey short         1                   500\n",
      "3        56        Slim-fit jean         2                   900\n",
      "4        57          White Shirt         3                  1200\n",
      "5        58           Mens Short         2                   600\n",
      "6        58  Cotton jersey short         5                   500\n",
      "7        59        Slim-fit jean         1                   900\n",
      "8        60          White Shirt         2                  1200\n",
      "9        61          White Shirt         2                  1200\n",
      "10       62           Mens Short         5                   600\n",
      "11       63  Cotton jersey short         3                   500\n",
      "customers:\n",
      "   customer_id    name  family_size\n",
      "0            1    John            1\n",
      "1            2   Cindy            6\n",
      "2            3  George            2\n",
      "3            4   Alice            3\n"
     ]
    }
   ],
   "source": [
    "#load sample tables as Pandas DataFrames, and add a few more example rows in checkouts and checkout_items for comprehensive testing:\n",
    "\n",
    "checkouts = pd.DataFrame({'customer_id' : [1, 2, 3, 4, 1, 2, 3, 4], 'cart_id' : [56, 57, 58, 59, 60, 61, 62, 63], 'date' : ['2021-01-02', '2021-01-02', '2021-01-08', '2021-02-01', '2021-02-15', '2021-02-05', '2021-02-28', '2021-03-08']})\n",
    "checkouts['date'] = pd.to_datetime(checkouts['date'], format=  '%Y-%m-%d') #format the dates as datetime objects so they can be used for queries/calculations\n",
    "print('checkouts:')\n",
    "print(checkouts)\n",
    "\n",
    "checkout_items = pd.DataFrame({'cart_id' : [56, 56, 56, 56, 57, 58, 58, 59, 60, 61, 62, 63], 'item' : ['White Shirt', 'Mens Short', 'Cotton jersey short', 'Slim-fit jean', 'White Shirt', 'Mens Short', 'Cotton jersey short', 'Slim-fit jean', 'White Shirt', 'White Shirt', 'Mens Short', 'Cotton jersey short'], 'quantity': [4, 1, 1, 2, 3, 2, 5, 1, 2, 2, 5, 3], 'price_per_unit_cents': [1200, 600, 500, 900, 1200, 600, 500, 900, 1200, 1200, 600, 500]})\n",
    "print('checkout_items:')\n",
    "print(checkout_items)\n",
    "\n",
    "customers = pd.DataFrame({'customer_id':[1, 2, 3, 4], 'name': ['John','Cindy','George','Alice'], 'family_size': [1, 6, 2, 3]})\n",
    "print('customers:')\n",
    "print(customers)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Excercise 1: \n",
    "\n",
    "**SQL**: What is monthly sales by customer type? Customer type defined as Single (1 family size), Couple (2 family size), Family (3-5), Large Family (6+)\n",
    "\n",
    "**Analysis Q** Why would J.Crew care about this? If Madewell's sales is high and increasing for Single family types, how should Madewell think about that? What else would you want to know?\n",
    "\n",
    "**My Answer**: This is a basic function that would allow J.Crew/Madewell to track which types of customers are making purchases, when those purchases and being made, how many purchases each group is making, the quantity of items bought and the amount of money spent by each group. This paints a picture of sales behavior which can be leveraged for all sorts of things: marketing campaigns, sales predictions, supply chain forecasting, etc. \n",
    "\n",
    "If Madewell's sales are high and increasing for Single family types, it shows us that the current marketing, onboarding, product quality & selection and price point are all *working* for this family type right now. Madewell could lean into this by increasing their marketing toward Single people, aiming to cast a wider net and onboard more new customers in this group. It would be important for Madewell to consider which products are most popular with this group and ensure their supply chain is equipped to handle the greater demand that would be the goal of such a marketing campaign. This could be investigated by tweaking the SQL query to include the specifics of which type of items are being purchased by this group over time: their quantity, cost, net profit to the company, etc. We could analyze the sales trend data to uncover insights into product popularity and quantify the bottom line for J.Crew/Madewell. In this way, we could uncover the types and quantity of products the company needs to make to keep up with demand, use data to inform future product development and identify marketing and J.Crew/Madewell crossover opportunities in this customer base.\n",
    "\n",
    "On the other hand, Madewell could consider the disparities between the Single family type and the other family types; perhaps there is market potential to capture more customers who fall into one of the other family types. I would probably start by comparing the monthly sales behavior of each group and identify the group(s) with the *most* similarity in purchasing behavior to the Single family type. Then, I'd investigate what the disparities are: do we simply have fewer customers of this group? Are they buying the same products or different products? Would it be reasonable for Madewell to increase production and marketing of the products this group *is* buying? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#This is the full query, as it would be written in MYSQL or PostgreSQL\n",
    "\n",
    "# WITH\n",
    "#  checkouts_fam AS (\n",
    "#    SELECT \n",
    "#    ch.customer_id, \n",
    "#    ch.cart_id, \n",
    "#    DATE_FORMAT(ch.date, '%m-%Y') AS month_year, \n",
    "#      CASE \n",
    "#        WHEN cu.family_size = 1 THEN 'Single'\n",
    "#        WHEN cu.family_size = 2 THEN 'Couple'\n",
    "#        WHEN cu.family_size > 5  THEN 'Large Family'\n",
    "#        ELSE 'Family'\n",
    "#      END AS customer_type,\n",
    "#     cu.family_size\n",
    "#    FROM checkouts AS ch \n",
    "#    LEFT JOIN customers AS cu \n",
    "#      ON ch.customer_id = cu.customer_id),\n",
    "#  transactions AS (\n",
    "#    SELECT \n",
    "#    cart_id, \n",
    "#    SUM((price_per_unit_cents * quantity)/100) AS trans_value_USD,\n",
    "#    SUM(quantity) AS n_items_purchased\n",
    "#    FROM checkout_items \n",
    "#    GROUP BY cart_id)\n",
    "# SELECT \n",
    "# customer_type, \n",
    "# month_year, \n",
    "# COUNT(cf.cart_id) AS total_monthly_cart_checkouts, \n",
    "# SUM(tr.n_items_purchased) AS total_monthly_items_puchased,\n",
    "# SUM(tr.trans_value_USD) AS total_monthly_trans_value_USD\n",
    "# FROM checkouts_fam AS cf\n",
    "# LEFT JOIN transactions AS tr \n",
    "#  ON cf.cart_id = tr.cart_id \n",
    "# GROUP BY customer_type, month_year\n",
    "# ORDER BY cf.family_size, month_year;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "checkouts_fam\n",
      "   customer_id  cart_id month_year customer_type  family_size\n",
      "0            1       56    01-2021        Single            1\n",
      "1            2       57    01-2021  Large Family            6\n",
      "2            3       58    01-2021        Couple            2\n",
      "3            4       59    02-2021        Family            3\n",
      "4            1       60    02-2021        Single            1\n",
      "5            2       61    02-2021  Large Family            6\n",
      "6            3       62    02-2021        Couple            2\n",
      "7            4       63    03-2021        Family            3\n"
     ]
    }
   ],
   "source": [
    "#check the contents of cte1\n",
    "print('checkouts_fam')\n",
    "print(ps.sqldf(\"SELECT ch.customer_id, ch.cart_id, strftime('%m-%Y', ch.date) AS month_year, CASE WHEN cu.family_size = 1 THEN 'Single' WHEN cu.family_size = 2 then 'Couple' WHEN cu.family_size >2 AND cu.family_size <6 then 'Family' ELSE 'Large Family' END AS customer_type, cu.family_size FROM checkouts AS ch LEFT JOIN customers AS cu ON ch.customer_id = cu.customer_id\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "transactions\n",
      "   cart_id  trans_value_USD  n_items_purchased\n",
      "0       56               77                  8\n",
      "1       57               36                  3\n",
      "2       58               37                  7\n",
      "3       59                9                  1\n",
      "4       60               24                  2\n",
      "5       61               24                  2\n",
      "6       62               30                  5\n",
      "7       63               15                  3\n"
     ]
    }
   ],
   "source": [
    "#Test the contents of cte2\n",
    "print('transactions')\n",
    "print(ps.sqldf(\"SELECT cart_id, SUM((price_per_unit_cents * quantity)/100) AS trans_value_USD, SUM(quantity) AS n_items_purchased FROM checkout_items GROUP BY cart_id\"))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Question1 Full Query\n",
      "  customer_type month_year  total_monthly_transactions  \\\n",
      "0        Single    01-2021                           1   \n",
      "1        Single    02-2021                           1   \n",
      "2        Couple    01-2021                           1   \n",
      "3        Couple    02-2021                           1   \n",
      "4        Family    02-2021                           1   \n",
      "5        Family    03-2021                           1   \n",
      "6  Large Family    01-2021                           1   \n",
      "7  Large Family    02-2021                           1   \n",
      "\n",
      "   total_monthly_items_puchased  total_monthly_trans_value_USD  \n",
      "0                             8                             77  \n",
      "1                             2                             24  \n",
      "2                             7                             37  \n",
      "3                             5                             30  \n",
      "4                             1                              9  \n",
      "5                             3                             15  \n",
      "6                             3                             36  \n",
      "7                             2                             24  \n"
     ]
    }
   ],
   "source": [
    "\n",
    "print('Question1 Full Query')\n",
    "full_query = \"WITH checkout_fam AS (SELECT ch.customer_id, ch.cart_id, strftime('%m-%Y', ch.date) AS month_year, CASE WHEN cu.family_size = 1 THEN 'Single' WHEN cu.family_size = 2 then 'Couple' WHEN cu.family_size >2 AND cu.family_size <6 then 'Family' ELSE 'Large Family' END AS customer_type, cu.family_size FROM checkouts AS ch LEFT JOIN customers AS cu ON ch.customer_id = cu.customer_id), transactions AS (SELECT cart_id, SUM((price_per_unit_cents * quantity)/100) AS trans_value_USD, SUM(quantity) AS n_items_purchased FROM checkout_items GROUP BY cart_id) SELECT cf.customer_type, cf.month_year, COUNT(cf.cart_id) AS total_monthly_transactions, SUM(tr.n_items_purchased) AS total_monthly_items_puchased, SUM(tr.trans_value_USD) AS total_monthly_trans_value_USD FROM checkout_fam AS cf JOIN transactions AS tr ON cf.cart_id = tr.cart_id GROUP BY cf.customer_type, cf.month_year ORDER BY cf.family_size, cf.month_year\"\n",
    "\n",
    "print(ps.sqldf(full_query))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Excercise 2: \n",
    "\n",
    "**SQL**: What is the average cart value by acquisition year cohort (e.g., what is the average cart value for customers acquired in 2019, 2020, 2021, etc.)\n",
    "\n",
    "**Analysis Q**: \n",
    "You find that average cart value is going down for later acquisition cohorts v. earlier — What are some hypotheses for why this is happening and what analysis would you run to test those hypotheses?\n",
    "\n",
    "**My Answer**:\n",
    " Some hypotheses: \n",
    " 1. Later aquisition cohorts may have less disposable income; e.g. they could be recent college grads vs. more-established working professionals from earlier cohorts. An analysis of the average cart value *over time* for each cohort would reveal any trends in support of this hypothesis. For instance, if we found that the average cart value of earlier cohorts increased over time, it would support that each cohort tends to buy a greater value of product over time. A line plot with average cart value over time, grouped by year cohort and normalized for the amount of time since signing up, would be a really nice way to visualize this. Analyzing demographic data for each cohort would also be helpful for this analysis, if we can get it. I'd ideally want to look at age, education, profession, income bracket, location (and cost of living in location), etc to test out this hypothesis and gain insights on customer behavior.\n",
    " \n",
    " 2. Later cohorts may be biased toward spending *less* at the same time, but how often are they buying? Does this group tend to purchase a lower cart value at a time, but make those purchases more often? How does this behavior (and the net profit to the company) compare to earlier cohorts? I would run an analysis to compare average number of cart purchases per month for each cohort to analyse purchase frequency behavior. I could multiply the avg frequency of purchases by the avg cart value to evaluate how each cohort is contributing to company profit. These figures could be compared between the cohort groups over time to evaluate any real differences in behavior. If we found, for instance, that later groups **do** prefer to purchase less at a time but more frequently, that would give the business valuable insight into how we may be able to increase the purchase volume for this type of customer--would offering free shipping on a lower cart value encourage this type of customer to buy smaller quantities even more frequently, e.g.? We could run an A|B test to find out.\n",
    "\n",
    " 3. If the frequency of purchases is heavily influenced by the cohort year (i.e. earlier cohorts are buying less frequently as time goes on vs. new cohorts), a change to the overall cost of the products over time could explain the observation. An analysis of average product cost along with average cart value over time could indicate whether the observation that later aquisition cohorts are spending less in each purchase is a reflection of changes to the cost-structure over time. I would couple this with an analysis of purchase frequency over time for each cohort, to evaluate the trends in cohort purchashing behavior as a function of how long they've been a customer, similar to in point 1, above.\n",
    "\n",
    " 4. Does our data cut off mid-year, thereby telling an incomplete story about the most recent cohort? If cart values go up significantly around the Holidays, but we don't have that data for the most recent cohort year, the simple explanation could be that we're not comparing apples to apples and need to exclude the current year's cohort from the analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# #full SQL query\n",
    "\n",
    "# WITH\n",
    "#    cart_vals AS(\n",
    "#        SELECT \n",
    "#        ch.customer_id,\n",
    "#        ch.cart_id, \n",
    "#        SUM((ci.price_per_unit_cents * quantity)/100) AS cart_value_USD \n",
    "#        FROM checkouts as ch\n",
    "#        LEFT JOIN checkout_items AS ci\n",
    "#        ON ci.cart_id = ch.cart_id\n",
    "#        GROUP BY ch.cart_id\n",
    "#        ),\n",
    "#    cohorts AS(\n",
    "#        SELECT \n",
    "#        customer_id,\n",
    "#        MIN(YEAR(date)) AS year_cohort\n",
    "#        FROM checkouts\n",
    "#        GROUP BY customer_id\n",
    "#        ) \n",
    "# SELECT \n",
    "# co.year_cohort,\n",
    "# AVG(cv.cart_value_USD) AS avg_cart_value_USD\n",
    "# FROM cart_vals AS cv\n",
    "# LEFT JOIN cohorts AS co\n",
    "# ON cv.customer_id = co.customer_id\n",
    "# GROUP BY co.year_cohort\n",
    "# ORDER BY co.year_cohort;\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "cart_vals\n",
      "   customer_id  cart_id  cart_value_USD\n",
      "0            1       56              77\n",
      "1            2       57              36\n",
      "2            3       58              37\n",
      "3            4       59               9\n",
      "4            1       60              24\n",
      "5            2       61              24\n",
      "6            3       62              30\n",
      "7            4       63              15\n"
     ]
    }
   ],
   "source": [
    "print(\"cart_vals\")\n",
    "print(ps.sqldf(\"SELECT ch.customer_id, ch.cart_id, SUM((ci.price_per_unit_cents * quantity)/100) AS cart_value_USD FROM checkouts AS ch LEFT JOIN checkout_items AS ci ON ci.cart_id = ch.cart_id GROUP BY ch.cart_id\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "cohorts\n",
      "   customer_id month_cohort\n",
      "0            1      01-2021\n",
      "1            2      01-2021\n",
      "2            3      01-2021\n",
      "3            4      02-2021\n"
     ]
    }
   ],
   "source": [
    "#note: using \"month cohort\" instead of \"year cohort\" for the purposes of testing in sqlite. Note also that the datetime methods look a little different\n",
    "\n",
    "print(\"cohorts\")\n",
    "print(ps.sqldf(\"SELECT customer_id, MIN(strftime('%m-%Y', date)) AS month_cohort FROM checkouts GROUP BY customer_id\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  month_cohort  average_cart_value_USD\n",
      "0           01                    38.0\n",
      "1           02                    12.0\n"
     ]
    }
   ],
   "source": [
    "print(ps.sqldf(\"WITH cart_vals AS (SELECT ch.customer_id, ch.cart_id, SUM((ci.price_per_unit_cents * quantity)/100) AS cart_value_USD FROM checkouts AS ch LEFT JOIN checkout_items AS ci ON ci.cart_id = ch.cart_id GROUP BY ch.cart_id), cohorts AS (SELECT customer_id, MIN(strftime('%m', date)) AS month_cohort FROM checkouts GROUP BY customer_id) SELECT co.month_cohort, AVG(cv.cart_value_USD) AS average_cart_value_USD FROM cart_vals AS cv LEFT JOIN cohorts AS co ON cv.customer_id = co.customer_id GROUP BY co.month_cohort ORDER BY co.month_cohort\"))\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Excercise 3:\n",
    "\n",
    "**SQL**: For each customer, what is the difference between the value of their first order and the second order?\n",
    "\n",
    "**Analysis Q**: Is this a useful metric? Why or why not? Feel free to suggest alternative metrics that could be interesting here!\n",
    "\n",
    "**My Answer**:\n",
    "I'd say this *might* be a useful metric, depending on the question we're trying to answer. If we want to know whether users spend more, less or the same amount on their second order compared to their first order, this is the right metric for that! This could help us understand user behavior to drive business decisions, and could be a concrete metric for A|B Testing, where we might assess whether a new marketing campaign, product launch, UX interface, brick-and-mortar store feature etc. encourages new users to spend more money on their second purchase, and thereby might be more likely to be happy repeat customers.\n",
    "\n",
    "We might also look at the amount of time between a customer's first and second purchases and the proportion of new customers who make a second purchase as an alternative way to assess our success with making new customers repeat customers. \n",
    "\n",
    "One of the problems with this metric is that it is most useful for new customers; we can use the data from the older customers as a historic reference. It might be interesting to look at these same metrics but with respect to the customers' *last two* purchases as a means of analyzing shopping behavior for existing customers. In this case our lens would probably be focused on strategies to increase profits through customer retention."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#full Sql query:\n",
    "\n",
    "<!-- \n",
    "WITH \n",
    "cart_vals AS(\n",
    "        SELECT \n",
    "        ch.customer_id,\n",
    "        ch.date AS time_of_purchase,\n",
    "        ch.cart_id, \n",
    "        SUM((ci.price_per_unit_cents * quantity)/100) AS cart_value_USD,\n",
    "        COUNT(ch.cart_id) OVER (PARTITION BY customer_id ORDER BY ch.date) AS order_number \n",
    "        FROM checkouts as ch\n",
    "        LEFT JOIN checkout_items AS ci\n",
    "        ON ci.cart_id = ch.cart_id\n",
    "        GROUP BY ch.cart_id\n",
    "        ),\n",
    "differences AS(\n",
    "        SELECT customer_id, \n",
    "        order_number,\n",
    "        cart_value_USD - LEAD(cart_value_USD) OVER(PARTITION BY customer_id ORDER BY order_number) AS difference \n",
    "        FROM cart_vals \n",
    "        WHERE order_number <= 2)\n",
    "SELECT \n",
    "customer_id,\n",
    "FIRST_VALUE(difference) OVER (PARTITION BY customer_id ORDER BY order_number) as value_of_first_order_minus_value_of_second_order_USD,\n",
    "FROM differences\n",
    "GROUP BY customer_id -->"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "checkouts:\n",
      "   customer_id  cart_id       date\n",
      "0            1       56 2021-01-02\n",
      "1            2       57 2021-01-02\n",
      "2            3       58 2021-01-08\n",
      "3            4       59 2021-02-01\n",
      "4            1       60 2021-02-15\n",
      "5            2       61 2021-02-05\n",
      "6            3       62 2021-02-28\n",
      "7            4       63 2021-03-08\n",
      "checkout_items:\n",
      "    cart_id                 item  quantity  price_per_unit_cents\n",
      "0        56          White Shirt         4                  1200\n",
      "1        56           Mens Short         1                   600\n",
      "2        56  Cotton jersey short         1                   500\n",
      "3        56        Slim-fit jean         2                   900\n",
      "4        57          White Shirt         3                  1200\n",
      "5        58           Mens Short         2                   600\n",
      "6        58  Cotton jersey short         5                   500\n",
      "7        59        Slim-fit jean         1                   900\n",
      "8        60          White Shirt         2                  1200\n",
      "9        61          White Shirt         2                  1200\n",
      "10       62           Mens Short         5                   600\n",
      "11       63  Cotton jersey short         3                   500\n",
      "customers:\n",
      "   customer_id    name  family_size\n",
      "0            1    John            1\n",
      "1            2   Cindy            6\n",
      "2            3  George            2\n",
      "3            4   Alice            3\n"
     ]
    }
   ],
   "source": [
    "#I'm just printing these here again for ease of viewing while constructing my query\n",
    "print('checkouts:')\n",
    "print(checkouts)\n",
    "\n",
    "print('checkout_items:')\n",
    "print(checkout_items)\n",
    "\n",
    "print('customers:')\n",
    "print(customers)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "cart_vals\n",
      "   customer_id            time_of_purchase  cart_id  cart_value_USD  \\\n",
      "0            1  2021-01-02 00:00:00.000000       56              77   \n",
      "1            1  2021-02-15 00:00:00.000000       60              24   \n",
      "2            2  2021-01-02 00:00:00.000000       57              36   \n",
      "3            2  2021-02-05 00:00:00.000000       61              24   \n",
      "4            3  2021-01-08 00:00:00.000000       58              37   \n",
      "5            3  2021-02-28 00:00:00.000000       62              30   \n",
      "6            4  2021-02-01 00:00:00.000000       59               9   \n",
      "7            4  2021-03-08 00:00:00.000000       63              15   \n",
      "\n",
      "   order_number  \n",
      "0             1  \n",
      "1             2  \n",
      "2             1  \n",
      "3             2  \n",
      "4             1  \n",
      "5             2  \n",
      "6             1  \n",
      "7             2  \n"
     ]
    }
   ],
   "source": [
    "print(\"cart_vals\")\n",
    "print(ps.sqldf(\"SELECT ch.customer_id, ch.date AS time_of_purchase, ch.cart_id, SUM((ci.price_per_unit_cents * quantity)/100) AS cart_value_USD, COUNT(ch.cart_id) OVER (PARTITION BY customer_id ORDER BY ch.date) AS order_number FROM checkouts as ch LEFT JOIN checkout_items AS ci ON ci.cart_id = ch.cart_id GROUP BY ch.cart_id ORDER BY customer_id, date\"))\n",
    "cart_vals= ps.sqldf(\"SELECT ch.customer_id, ch.date AS time_of_purchase, ch.cart_id, SUM((ci.price_per_unit_cents * quantity)/100) AS cart_value_USD, COUNT(ch.cart_id) OVER (PARTITION BY customer_id ORDER BY ch.date) AS order_number FROM checkouts as ch LEFT JOIN checkout_items AS ci ON ci.cart_id = ch.cart_id GROUP BY ch.cart_id ORDER BY customer_id, date\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "differences\n",
      "   customer_id  order_number  difference\n",
      "0            1             1        53.0\n",
      "1            1             2         NaN\n",
      "2            2             1        12.0\n",
      "3            2             2         NaN\n",
      "4            3             1         7.0\n",
      "5            3             2         NaN\n",
      "6            4             1        -6.0\n",
      "7            4             2         NaN\n"
     ]
    }
   ],
   "source": [
    "print('differences')\n",
    "print(ps.sqldf(\"SELECT customer_id, order_number, cart_value_USD - LEAD(cart_value_USD) OVER(PARTITION BY customer_id ORDER BY order_number) AS difference FROM cart_vals WHERE order_number <= 2\"))\n",
    "\n",
    "differences = ps.sqldf(\"SELECT customer_id, order_number, cart_value_USD - LEAD(cart_value_USD) OVER(PARTITION BY customer_id ORDER BY order_number) AS difference FROM cart_vals WHERE order_number <= 2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "final result\n",
      "   customer_id  value_of_first_order_minus_value_of_second_order_USD\n",
      "0            1                                               53.0   \n",
      "1            2                                               12.0   \n",
      "2            3                                                7.0   \n",
      "3            4                                               -6.0   \n"
     ]
    }
   ],
   "source": [
    "print('final result')\n",
    "print(ps.sqldf(\"SELECT customer_id, FIRST_VALUE(difference) OVER (PARTITION BY customer_id ORDER BY order_number) as value_of_first_order_minus_value_of_second_order_USD FROM differences GROUP BY customer_id\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   customer_id  value_of_first_order_minus_value_of_second_order_USD\n",
      "0            1                                                 53   \n",
      "1            2                                                 12   \n",
      "2            3                                                  7   \n",
      "3            4                                                 -6   \n"
     ]
    }
   ],
   "source": [
    "#All together now!\n",
    "print(ps.sqldf(\"WITH cart_vals AS (SELECT ch.customer_id, ch.date, ch.cart_id, SUM((ci.price_per_unit_cents * quantity)/100) AS cart_value_USD, COUNT(ch.cart_id) OVER (PARTITION BY customer_id ORDER BY ch.date) AS order_number FROM checkouts as ch LEFT JOIN checkout_items AS ci ON ci.cart_id = ch.cart_id GROUP BY ch.cart_id ORDER BY customer_id, date), differences AS (SELECT customer_id, order_number, cart_value_USD - LEAD(cart_value_USD) OVER(PARTITION BY customer_id ORDER BY order_number) AS difference FROM cart_vals WHERE order_number <= 2) SELECT customer_id, FIRST_VALUE(difference) OVER (PARTITION BY customer_id ORDER BY order_number) as value_of_first_order_minus_value_of_second_order_USD FROM differences GROUP BY customer_id \"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I look forward to continuing to learn how to make my SQL queries quick and efficient!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9.7 ('base')",
   "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"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "94a9c7a724586976c069325963d5f127e5872c3f68c18346cb196499888c15b4"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}