Case Study #3 — Foodie-Fi

I just completed Week 3 of the #8weekSQLchallenge by Danny Ma. You can check out the challenge here!
I really enjoy working with SQL. After completing Case Study 1 and 2, I am more confident with SQL and some of the functionality came to me quite naturally.

Section A. Customer Journey

Question 1 — Based off the 8 sample customers provided in the sample from the subscriptions table, write a brief description about each customer’s onboarding journey.

Result

Section B. Data Analysis

Question 1 — How many customers has Foodie-Fi ever had?

customer count

Result

Question 2 — What is the monthly distribution of trial plan start_date values for our dataset — use the start of the month as the group by value

Query

Result

Results visualised in Tableau

Question 3 — What plan start_date values occur after the year 2020 for our dataset? Show the breakdown by count of events for each plan_name

There are two events for each Plan Name — Joining a new plan and upgrading/downgrading it, i.e. cancelling it. I created CTEs for both the events and got the result by UNION

SQL query

Question 4 — What is the customer count and percentage of customers who have churned rounded to 1 decimal place?

Counting Churned Customers

Question 5 — How many customers have churned straight after their initial free trial — what percentage is this rounded to the nearest whole number?

Question 6 — What is the number and percentage of customer plans after their initial free trial?

plans after trial

Result

Count for each Plan type after trial

Results visualised (Tableau)

Question 7 — What is the customer count and percentage breakdown of all 5 plan_name values at 2020–12–31?

Results

Question 8 — How many customers have upgraded to an annual plan in 2020?

Question 9 — How many days on average does it take for a customer to an annual plan from the day they join Foodie-Fi?

Question 10 — Can you further breakdown this average value into 30 day periods (i.e. 0–30 days, 31–60 days etc)

Question 11 — How many customers downgraded from a pro monthly to a basic monthly plan in 2020?