8 Week SQL Challenge - Case Study 3 (Outside The Box Questions)

It is great to be back. I have been under the weather for the past few days. Dear Harmattan, I hope you are finally in because my body is not adjusting well to this inconsistency of heat one day and cold the other.

In this article, the final area of Case Study #3 - Foodie-Fi is on Outside The Box Questions. A glance through them and I love the fact that these questions are where I am expected to display my business knowledge. I do not have a lot, but I am hoping to share what I have since has been the expectation for this SQL series.

How would you calculate the rate of growth for Foodie-Fi?

I would like to use the percentage of customer growth (Customer Growth Rate) each month. Here, the SQL statement will return a table with:

• First day of each month of the year

• Total number of new customers who signed up that month

• Total number of customers who signed up the previous month

• Percentage growth, negative meaning less customers signed up in the current and positive means more customers signed up this month than the previous month

WITH monthly_customers AS (
    SELECT
        DATE_FORMAT(start_date, '%Y-%m') AS month,
        COUNT(DISTINCT customer_id) AS new_customers
    FROM subscriptions
    WHERE plan_id = 0
    GROUP BY month
),
growth_calc AS (
    SELECT
        month,
        new_customers,
        LAG(new_customers) OVER (ORDER BY month) AS prev_month_customers,
        ROUND(100 * (new_customers - LAG(new_customers) OVER (ORDER BY month)) / LAG(new_customers) OVER (ORDER BY month), 2) AS percent_growth_rate
    FROM
    monthly_customers
)

SELECT 
*
FROM growth_calc
WHERE prev_month_customers IS NOT NULL;

What key metrics would you recommend Foodie-Fi management to track over time to assess performance of their overall business?

The metric I will want to keep track of is the Monthly Recurring Revenue. This refers to the total amount of money received per month for all customers who are neither on a trial nor churn (0/4) plan.

SELECT
    DATE_FORMAT(subscriptions.start_date, '%Y-%m') AS month,
    ROUND(SUM(
        CASE 
            WHEN plans.plan_id = 1 THEN plans.price
            WHEN plans.plan_id = 2 THEN plans.price
            WHEN plans.plan_id = 3 THEN plans.price / 12
        END
    ), 2) AS mrr
FROM subscriptions
INNER JOIN plans
    ON subscriptions.plan_id = plans.plan_id
WHERE plans.plan_id IN (1, 2, 3)
GROUP BY month
ORDER BY month;

What are some key customer journeys or experiences that you would analyse further to improve customer retention?

Here I want to look at the Trial to Conversion journey, from the customer’s subscription information, how many exhausted the full trial period? From those who used up the full trial period, how many purchased a plan?

WITH conversion_timing AS (
    SELECT 
        s1.customer_id,
        s1.start_date AS trial_start,
        MIN(s2.start_date) AS conversion_date,
        DATEDIFF(MIN(s2.start_date), s1.start_date) AS days_to_convert,
        MIN(s2.plan_id) AS first_paid_plan
    FROM subscriptions s1
    LEFT JOIN subscriptions s2
        ON s1.customer_id = s2.customer_id AND s2.plan_id IN (1,2,3)
    WHERE s1.plan_id = 0
    GROUP BY s1.customer_id, s1.start_date
)

SELECT
    CASE
        WHEN days_to_convert = 7 THEN '7 days'
        ELSE 'Did not convert'
    END AS conversion_duration,
    COUNT(*) AS total_customers,
    COUNT(conversion_date) AS conversions,
    ROUND(100 * COUNT(conversion_date) / COUNT(*), 2) AS conversion_rate
FROM conversion_timing
GROUP BY conversion_duration
ORDER BY conversion_duration;