8 Week SQL Challenge - Case Study 3 (Data Analysis Questions)

We are in the second section of Case 3: Data Analysis Questions where we have a total of 11 questions to answer. Onwards we go ⏩

How many customers has Foodie-Fi ever had?

SELECT 
    COUNT(DISTINCT customer_id) AS total_number_of_customers
FROM subscriptions;

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

SELECT 
    DATE_FORMAT(subscriptions.start_date, '%Y-%m-01') AS trial_month,
    COUNT(*) AS trial_count
FROM subscriptions
WHERE plan_id = 0
GROUP BY trial_month
ORDER BY trial_month;

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

SELECT 
    plans.plan_name,
    COUNT(*) AS plan_count
FROM subscriptions
INNER JOIN plans
    ON subscriptions.plan_id = plans.plan_id
WHERE YEAR(subscriptions.start_date) > 2020
GROUP BY plans.plan_name
ORDER BY plans.plan_name;

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

SELECT 
        COUNT(DISTINCT subscriptions.customer_id) AS churned_customers,
        ROUND(100 * COUNT(DISTINCT subscriptions.customer_id) / (SELECT COUNT(DISTINCT subscriptions.customer_id) FROM subscriptions), 1) AS percentage_churned_customers
FROM subscriptions
WHERE subscriptions.plan_id = 4;

We create a temporary table called RANKED_PLANS for the next set of questions where for each customer, we assign a row number for each of the start dates for their plans.
This will make getting plan information such as what was their next plan after trial easy.

CREATE TEMPORARY TABLE ranked_plans (
SELECT
    customer_id,
    plan_id,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY start_date) AS plan_rank
FROM subscriptions);

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

SELECT 
    COUNT(customer_id) AS total_churned_after_free_trial,
    ROUND(100 * COUNT(DISTINCT customer_id) / (SELECT COUNT(DISTINCT customer_id) FROM subscriptions), 0) AS percentage_churned_after_free_trial
FROM ranked_plans
WHERE plan_rank = 2 AND plan_id = 4;

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

SELECT 
    plans.plan_name,
    COUNT(*) AS total_customers_on_plan_after_trial,
    ROUND(100 * COUNT(*) / (SELECT COUNT(DISTINCT customer_id) FROM subscriptions), 1) AS percentage_customers_under_plan_after_trial
FROM ranked_plans
INNER JOIN plans
    ON ranked_plans.plan_id = plans.plan_id
WHERE ranked_plans.plan_rank > 1
GROUP BY plans.plan_id, plans.plan_name
ORDER BY plans.plan_id;

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

SELECT 
    COUNT(DISTINCT customer_id) AS total_annual_pkg_customers
FROM subscriptions
WHERE subscriptions.start_date >= '2020-01-01' AND subscriptions.start_date < '2021-01-01' AND plan_id = 3;