{"id":221,"date":"2022-06-16T11:59:50","date_gmt":"2022-06-16T11:59:50","guid":{"rendered":"https:\/\/giuliopulino.com\/?p=221"},"modified":"2022-07-30T11:09:39","modified_gmt":"2022-07-30T11:09:39","slug":"project_foodie-fi","status":"publish","type":"post","link":"https:\/\/giuliopulino.com\/index.php\/2022\/06\/16\/project_foodie-fi\/","title":{"rendered":"Case Study FOODIE – FI"},"content":{"rendered":"\n

This case study focuses on using subscription style digital data to answer important business questions.<\/p>\n\n\n\n

<\/p>\n\n\n\n

The Database and the entire case study is available at the following link: https:\/\/8weeksqlchallenge.com <\/a>, in this page we will focus directly to answer the business questions.<\/p>\n\n\n\n

<\/p>\n\n\n\n

THE DATATABASE<\/strong><\/h3>\n\n\n\n

Just a quick overview of the database, we have 2 tables, connected by “plan_id”.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n

Let’s jump directly to answer the business questions<\/p>\n\n\n\n

<\/p>\n\n\n\n

How many customers has Foodie-Fi ever had?<\/strong><\/p>\n\n\n

\nSELECT \n\tCOUNT(DISTINCT customer_id) AS total_customers\nFROM subscriptions;\n<\/pre><\/div>\n\n\n
\"\"
Answer: There are 1000 unique customers.<\/figcaption><\/figure>\n\n\n\n
————————————————————<\/p>\n\n\n\n
What is the monthly distribution of trial<\/code> plan start_date<\/code> values for our dataset ?<\/strong><\/p>\n\n\n
\nSELECT\n\tYEAR(start_date) AS year_,\n    MONTH(start_date) AS m_,\n\tMONTHNAME(start_date) AS month_,\n\tCOUNT(plan_name) AS trial_plans\nFROM plans p\n\tLEFT JOIN subscriptions s\n\t\tON p.plan_id = s.plan_id\nWHERE plan_name = "trial"\nGROUP BY 1,2,3\nORDER BY 1,2;\n<\/pre><\/div>\n\n\n
\"\"<\/figure>\n\n\n\n
————————————————————<\/strong><\/p>\n\n\n\n
What plan start_date<\/code> values occur after the year 2020 for our dataset? <\/strong><\/p>\n\n\n
\nSELECT\n\t\tp.plan_name,\n        COUNT(s.customer_id)\n\tFROM plans p\n\t\tLEFT JOIN subscriptions s\n\t\t\tON p.plan_id = s.plan_id\n\tWHERE start_date > "2020-12-31"\n    GROUP BY 1;\n<\/pre><\/div>\n\n\n
\"\"<\/figure>\n\n\n\n
————————————————————<\/p>\n\n\n\n
What is the customer count and percentage of customers who have churned rounded to 1 decimal place?<\/strong><\/p>\n\n\n
\nSELECT\n\tCOUNT(DISTINCT customer_id) AS total_customers,\n    COUNT(CASE WHEN plan_id = 4 THEN 1 ELSE NULL END) AS churned_customers,\n    ROUND(COUNT(CASE WHEN plan_id = 4 THEN 1 ELSE NULL END) \/\n\t\tCOUNT(DISTINCT customer_id) * 100,1) AS pct_churn\nFROM subscriptions;\n<\/pre><\/div>\n\n\n
\"\"<\/figure>\n\n\n\n
————————————————————<\/p>\n\n\n\n
How many customers have churned straight after their initial free trial – what percentage is this rounded to the nearest whole number?<\/strong><\/p>\n\n\n\n
To answer this question I run before distinct queries and then combine all of them in a single query with a subquery, the idea was to generate a list with all customers after 1 week, and join this list with the subscriptions table by customer_id so that we can obtain their plan status after 1 week and then just calculate the total and the percentage. <\/p>\n\n\n\n
It’s a subquery, so we’ll start to read from the bottom, I left comments with the order on how to read the query, from 1 to 3<\/p>\n\n\n
\nSELECT -- 3 count the number of total customers, and the churned customers (plan_id = 4)\n\tCOUNT(customer_id) AS total_customers,\n    COUNT(CASE WHEN plan_id = 4 THEN 1 ELSE NULL END) AS church_after_trial,\n    CEILING(COUNT(CASE WHEN plan_id = 4 THEN 1 ELSE NULL END) \/ COUNT(customer_id) * 100) AS pct_of_church_after_trial\nFROM \n(\nSELECT -- 2 we join the list(1) with the subscriptions table so that we can see their status\n\ta.customer_id,\n    a.after_1_week,\n    s.plan_id\nFROM \n(\nSELECT -- 1 generate a list with all customers after 1 week \n\tcustomer_id AS customer_id,\n\tDATE_ADD(MIN(start_date), INTERVAL 7 DAY) AS after_1_week\nFROM subscriptions s\n\tLEFT JOIN plans p\n\t\tON s.plan_id = s.plan_id\nGROUP BY 1\nORDER BY 1\n) AS a\n\tLEFT JOIN subscriptions s\n\t\tON a.customer_id = s.customer_id AND\n\t\t\ta.after_1_week = s.start_date) AS b;\n<\/pre><\/div>\n\n\n
\"\"<\/figure>\n\n\n\n
————————————————————<\/p>\n\n\n\n
<\/div>\n\n\n\n
<\/div>\n\n\n\n
What is the number and percentage of customer plans after their initial free trial?<\/strong><\/p>\n\n\n\n
We can use the same query used above to obtain all the plan id of the customers after the end of the trial period and use it in a subquery to calculate the percentage.<\/p>\n\n\n
\nSELECT\n\tCOUNT(CASE WHEN plan_id = 1 THEN 1 ELSE NULL END) AS basic_monthly,\n    ROUND((COUNT(CASE WHEN plan_id = 1 THEN 1 ELSE NULL END) \/ COUNT(customer_id)) * 100,2) AS pct_basic_monthly,\n\tCOUNT(CASE WHEN plan_id = 2 THEN 1 ELSE NULL END) AS pro_monthly,\n    ROUND((COUNT(CASE WHEN plan_id = 2 THEN 1 ELSE NULL END) \/ COUNT(customer_id)) * 100,2) AS pct_pro_monthly,\n\tCOUNT(CASE WHEN plan_id = 3 THEN 1 ELSE NULL END) AS pro_annual,\n    ROUND((COUNT(CASE WHEN plan_id = 3 THEN 1 ELSE NULL END) \/ COUNT(customer_id)) * 100,2) AS pct_pro_annual,\n\tCOUNT(CASE WHEN plan_id = 4 THEN 1 ELSE NULL END) AS churn,\n    ROUND((COUNT(CASE WHEN plan_id = 4 THEN 1 ELSE NULL END) \/ COUNT(customer_id)) * 100,2) AS pct_churn\nFROM\n(\nSELECT\n\ta.customer_id,\n    a.after_1_week,\n    s.plan_id\nFROM \n(\nSELECT \n\tcustomer_id AS customer_id,\n\tDATE_ADD(MIN(start_date), INTERVAL 7 DAY) AS after_1_week\nFROM subscriptions s\n\tLEFT JOIN plans p\n\t\tON s.plan_id = s.plan_id\nGROUP BY 1\nORDER BY 1\n) AS a\n\tLEFT JOIN subscriptions s\n\t\tON a.customer_id = s.customer_id AND\n\t\t\ta.after_1_week = s.start_date) AS b; \n<\/pre><\/div>\n\n\n
\"\"<\/figure>\n\n\n\n
————————————————————<\/p>\n\n\n\n
What is the customer count and percentage breakdown of all 5 plan_name<\/code> values at 2020-12-31<\/code>?<\/strong><\/p>\n\n\n\n
The process it’s very similar to the previous one, we generate a list of all customers after their trial period and before the end of 2020-12-31 with the MAX date (so the last update of their subscription status), then we join the result with the subscriptions table to have their status and finally we calculate the number of customers and percentage.<\/p>\n\n\n
\nSELECT\n\tCOUNT(CASE WHEN plan_id = 0 THEN 1 ELSE NULL END) AS trial,\n    ROUND((COUNT(CASE WHEN plan_id = 0 THEN 1 ELSE NULL END) \/ COUNT(customer_id)) * 100,2) AS pct_trial,\n    COUNT(CASE WHEN plan_id = 1 THEN 1 ELSE NULL END) AS basic_monthly,\n    ROUND((COUNT(CASE WHEN plan_id = 1 THEN 1 ELSE NULL END) \/ COUNT(customer_id)) * 100,2) AS pct_basic_monthly,\n    COUNT(CASE WHEN plan_id = 2 THEN 1 ELSE NULL END) AS pro_monthly,\n    ROUND((COUNT(CASE WHEN plan_id = 2 THEN 1 ELSE NULL END) \/ COUNT(customer_id)) * 100,2) AS pct_pro_monthly,\n    COUNT(CASE WHEN plan_id = 3 THEN 1 ELSE NULL END) AS pro_annual,\n    ROUND((COUNT(CASE WHEN plan_id = 3 THEN 1 ELSE NULL END) \/ COUNT(customer_id)) * 100,2) AS pct_pro_annual,\n    COUNT(CASE WHEN plan_id = 4 THEN 1 ELSE NULL END) AS churn,\n    ROUND((COUNT(CASE WHEN plan_id = 4 THEN 1 ELSE NULL END) \/ COUNT(customer_id)) * 100,2) AS pct_churn\nFROM(\n\nSELECT -- 2 we join the list(1) with the subscriptions table so that we can see their status\n    a.customer_id,\n    a.after_1_week,\n    s.plan_id\nFROM (\nSELECT -- 1 generate a list with all customers after 1 week and before the 2020-12-31\n\tcustomer_id AS customer_id,\n    MAX(start_date) AS after_1_week\nFROM subscriptions s\n    LEFT JOIN plans p\n        ON s.plan_id = s.plan_id\nWHERE start_date < "2020-12-31"\nGROUP BY 1\nORDER BY 1)\nAS a\n    LEFT JOIN subscriptions s\n        ON a.customer_id = s.customer_id AND\n            a.after_1_week = s.start_date) AS b;\n<\/pre><\/div>\n\n\n
\"\"<\/figure>\n\n\n\n
How many customers have upgraded to an annual plan in 2020?<\/strong><\/p>\n\n\n
\nSELECT\n\tCOUNT(plan_id) AS total_pro_annual_2020\nFROM subscriptions\nWHERE plan_id = 3\n\tAND start_date <= "2020-12-31";\n<\/pre><\/div>\n\n\n
\"\"<\/figure>\n\n\n\n
————————————————————<\/p>\n\n\n\n
How many days on average does it take for a customer to an annual plan from the day they join Foodie-Fi?<\/strong><\/p>\n\n\n\n
I solved this one in 2 ways, one was to create 2 temporary tables, one with the only trial customers and the other one with the pro customers, then subtract the dates and calculate the average.<\/p>\n\n\n\n
The other solution was using CTE, I’ll show you the CTE version, we’ll use the temporary tables for the next questions.<\/p>\n\n\n\n
<\/p>\n\n\n
\nWITH trial_users AS (\nSELECT \n\ts.customer_id,\n    s.plan_id,\n    s.start_date,\n    p.plan_name\nFROM subscriptions s\n\tLEFT JOIN plans p\n\t\tON s.plan_id = p.plan_id\nWHERE plan_name = "trial"\nORDER BY 1),\npro_users AS (\nSELECT\n\tcustomer_id,\n    plan_id,\n    start_date,\n    plan_name\nFROM (\nSELECT \n\ts.customer_id,\n    s.plan_id,\n    s.start_date,\n    p.plan_name\nFROM subscriptions s\n\tLEFT JOIN plans p\n\t\tON s.plan_id = p.plan_id\nORDER BY 1) AS a\nWHERE plan_name = "pro annual"\n)\nSELECT\n\tCEILING(AVG(days_to_became_pro)) AS average_days_to_pro\nFROM (\n\tSELECT \n\t\tpu.customer_id,\n        DATEDIFF(pu.start_date, tu.start_date) AS days_to_became_pro\n\tFROM pro_users pu\n\t\tLEFT JOIN trial_users tu\n\t\t\tON pu.customer_id = tu.customer_id) AS f;\n<\/pre><\/div>\n\n\n
\"\"<\/figure>\n\n\n\n
————————————————————<\/p>\n\n\n\n
Can you further breakdown this average value into 30 day periods (i.e. 0-30 days, 31-60 days etc)<\/strong><\/p>\n\n\n\n
<\/p>\n\n\n\n
I created 2 temporary tables as anticipated in the previous question, one with the PRO customers and the other one with the TRIAL customers, then join the 2 table and insert them in an inner query and calculate the average for each single period<\/p>\n\n\n
\n-- Create Temporary Table TRIAL customers\nCREATE TEMPORARY TABLE trial_customers\nSELECT\n\tcustomer_id,\n    plan_id,\n    start_date,\n    plan_name\nFROM (\nSELECT \n\ts.customer_id,\n    s.plan_id,\n    s.start_date,\n    p.plan_name\nFROM subscriptions s\n\tLEFT JOIN plans p\n\t\tON s.plan_id = p.plan_id\nORDER BY 1) AS a\nWHERE plan_name = "trial";\n\n\n-- Create Temporary Table PRO Customers\nCREATE TEMPORARY TABLE pro_annual_customers\nSELECT\n\tcustomer_id,\n    plan_id,\n    start_date,\n    plan_name\nFROM (\nSELECT \n\ts.customer_id,\n    s.plan_id,\n    s.start_date,\n    p.plan_name\nFROM subscriptions s\n\tLEFT JOIN plans p\n\t\tON s.plan_id = p.plan_id\nORDER BY 1) AS a\nWHERE plan_name = "pro annual";\n\n-- Join the 2 temporary table, insert them in a inner query and calculate the average for each period\nSELECT\n\tCEILING(AVG(period_0_30)) AS avg_0_30,\n    CEILING(AVG(period_31_60)) AS avg_31_60,\n    CEILING(AVG(period_61_120)) avg_61_120,\n    CEILING(AVG(period_more_than_120)) AS avg_over_120\nFROM (\nSELECT\n\tCASE WHEN DATEDIFF(pro.start_date, trial.start_date) <= 30 THEN DATEDIFF(pro.start_date, trial.start_date) ELSE NULL END AS period_0_30,\n    CASE WHEN DATEDIFF(pro.start_date, trial.start_date) > 30 AND DATEDIFF(pro.start_date, trial.start_date) <= 60 THEN DATEDIFF(pro.start_date, trial.start_date) ELSE NULL END AS period_31_60,\n    CASE WHEN DATEDIFF(pro.start_date, trial.start_date) > 60 AND DATEDIFF(pro.start_date, trial.start_date) <= 120 THEN DATEDIFF(pro.start_date, trial.start_date) ELSE NULL END AS period_61_120,\n    CASE WHEN DATEDIFF(pro.start_date, trial.start_date) > 120 THEN DATEDIFF(pro.start_date, trial.start_date) ELSE NULL END AS period_more_than_120\nFROM pro_annual_customers pro\n\tLEFT JOIN trial_customers trial\n\t\tON pro.customer_id = trial.customer_id) AS a;\n<\/pre><\/div>\n\n\n
\"\"<\/figure>\n\n\n\n
————————————————————<\/p>\n\n\n\n
How many customers downgraded from a pro monthly to a basic monthly plan in 2020?<\/strong><\/p>\n\n\n\n
I created 2 new different temporary tables, one with the PRO monthly plans, and the other one with the BASIC monthly plans where the date is greater than in the previous table.<\/p>\n\n\n
\n-- create temporary table PRO MONTHLY PLANS\nCREATE TEMPORARY TABLE pro_monthly\nSELECT\n\tcustomer_id,\n    plan_id,\n    start_date,\n    plan_name\nFROM (\nSELECT \n\ts.customer_id,\n    s.plan_id,\n    s.start_date,\n    p.plan_name\nFROM subscriptions s\n\tLEFT JOIN plans p\n\t\tON s.plan_id = p.plan_id\nORDER BY 1) AS a\nWHERE plan_name = "pro monthly";\n\n-- create temporary table BASIC MONTHLY PLANS\nSELECT \n\tcustomer_id,\n    plan_id,\n    start_date,\n    plan_name\nFROM (\nSELECT \n\ts.customer_id,\n    s.plan_id,\n    s.start_date,\n    p.plan_name\nFROM subscriptions s\n\tLEFT JOIN plans p\n\t\tON s.plan_id = p.plan_id\nORDER BY 1) AS b\nWHERE plan_name = "basic monthly";\n\n\n-- combine the 2 tables and calculate\n\nSELECT\n\tCOUNT(pro.customer_id) AS downgraded_customers\nFROM pro_monthly pro\n\tINNER JOIN basic_monthly basic\n\t\tON pro.customer_id = basic.customer_id\n\t\t\tWHERE basic.start_date > pro.start_date;\n<\/pre><\/div>\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Thanks for your time.<\/p>\n\n\n\n
<\/p>\n\n\n\n
Giulio Pulino.<\/p>\n\n\n\n Back to portfolio <\/a>\n","protected":false},"excerpt":{"rendered":"
This case study focuses on using subscription style digital data to answer important business questions. The Database and the entire case study is available at the following link: https:\/\/8weeksqlchallenge.com , in this page we will focus directly to answer the business questions. THE DATATABASE Just a quick overview of the database, we have 2 tables, …<\/p>\n
  Case Study FOODIE – FI<\/span> Read More »<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"site-sidebar-layout":"default","site-content-layout":"default","ast-global-header-display":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":""},"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/giuliopulino.com\/index.php\/wp-json\/wp\/v2\/posts\/221"}],"collection":[{"href":"https:\/\/giuliopulino.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/giuliopulino.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/giuliopulino.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/giuliopulino.com\/index.php\/wp-json\/wp\/v2\/comments?post=221"}],"version-history":[{"count":32,"href":"https:\/\/giuliopulino.com\/index.php\/wp-json\/wp\/v2\/posts\/221\/revisions"}],"predecessor-version":[{"id":863,"href":"https:\/\/giuliopulino.com\/index.php\/wp-json\/wp\/v2\/posts\/221\/revisions\/863"}],"wp:attachment":[{"href":"https:\/\/giuliopulino.com\/index.php\/wp-json\/wp\/v2\/media?parent=221"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/giuliopulino.com\/index.php\/wp-json\/wp\/v2\/categories?post=221"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/giuliopulino.com\/index.php\/wp-json\/wp\/v2\/tags?post=221"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}