Introduction
Hey everyone, welcome back to the channel. If you've been following along with this series, we've already worked through Parts A and B of Case Study 3 from the 8 Week SQL Challenge: Foodie-Fi. If you haven't seen those yet, I'll leave the links in the description below.
Today we are tackling the final two parts; C and D. Now I want to be upfront with you These are the most challenging sections of this case study. Part C in particular is one of those problems that looks simple on the surface but requires you to really think carefully about your approach. So we're going to take it slow, break everything down, and by the end of this video you'll have a solid understanding of not just the solution, but why the solution works the way it does.
All queries in this video are written for Microsoft SQL Server. Let's get into it.
Before we write a single line of code, let's make sure we fully understand what we're being asked to do. This is something I always encourage, read the problem at least twice before touching your keyboard.
The Foodie-Fi team wants us to create a payments table for the year 2020. Sounds simple, right? But there are some specific rules we need to follow, and these rules are what make this problem genuinely interesting.
Here are the rules:
Rule 1: Monthly plan customers are charged every month from their start date until the end of 2020 or until they change or cancel their plan.
Rule 2: Annual plan customers are only charged once: on the date they signed up for the annual plan.
Rule 3: If a customer upgrades from a basic monthly plan to either the pro monthly or pro annual plan during a month where they've already been charged, they only pay the difference in price. So for example, if someone is on the basic plan at $9.90 and upgrades to pro monthly at $19.90, they only pay $10.00 extra that month, not the full $19.90.
Rule 4: Customers who churn stop making payments entirely.
Take a moment to let those rules sink in.
The tricky parts are Rule 1 because we need to generate multiple payment rows for monthly customers and Rule 3, which requires us to look back at what a customer already paid.
The Approach
So how do we solve this? We're going to use two CTEs and a final SELECT statement.
For those who are newer to SQL, a CTE stands for Common Table Expression.
Think of it as a temporary named result set that you can reference within your query.
It's like building a query in stages, where each stage builds on the previous one.
Here's our game plan:
Stage 1 → Get each customer's plan details + when their NEXT plan starts (this tells us the billing window)
Stage 2 → Use a Recursive CTE to loop through months and generate a payment row for each billing period
Final → Calculate the actual payment amount, applying the upgrade discount where needed
Let's walk through each stage.
CTE 1 - Customer Plans
-- ============================================================
-- CTE 1: customer_plans
-- ============================================================
-- PURPOSE:
-- For each customer, get their plan details AND the date
-- their next plan starts. This gives us the "window" of time
-- they were on each plan, which tells us when to stop
-- generating payments for that plan.
-- ============================================================
WITH customer_plans AS (
SELECT
s.customer_id,
s.plan_id,
p.plan_name,
p.price,
s.start_date,
-- LEAD() looks ahead to the next row for the same customer
-- It gives us the start date of their NEXT plan
-- If there is no next plan, we default to Dec 31 2020
ISNULL(
LEAD(s.start_date) OVER (
PARTITION BY s.customer_id -- Look within each customer
ORDER BY s.start_date -- In chronological order
),
'2020-12-31') AS next_plan_date
FROM subscriptions s
JOIN plans p ON s.plan_id = p.plan_id
-- We exclude plan_id 0 (trial) because trials are free
-- We exclude plan_id 4 (churn) because churned customers
-- don't make any payments
WHERE s.plan_id NOT IN (0, 4)
AND YEAR(s.start_date) = 2020
)Let me explain the most important piece here, the LEAD() window function.
LEAD() is a window function that lets you look at the next row in a result set without doing a self-join.
We're partitioning by customer_id, meaning we look at the next row for the same customer and ordering by start_date so we get rows in chronological order.
So if a customer started on basic monthly in January and then upgraded to pro monthly in June, the LEAD() on the January row returns June's date. That June date becomes our next_plan_date and it tells us to stop generating basic monthly payments after May.
If a customer never changes their plan, LEAD() returns NULL which we handle with ISNULL(), defaulting to December 31st 2020.
CTE 2 - The Recursive CTE
This is where things get really interesting. And honestly, if you've never used a recursive CTE before, this is going to be one of those moments where something just clicks.
-- ============================================================
-- CTE 2: payments (Recursive CTE)
-- ============================================================
-- PURPOSE:
-- Generate one payment row per billing period per customer.
-- For monthly plans this means one row per month.
-- For the annual plan this means just one row.
--
-- HOW A RECURSIVE CTE WORKS:
-- Think of it like a while loop in programming.
-- It has two parts:
-- ANCHOR = the starting point (first payment)
-- RECURSIVE = keeps running, adding 1 month each time,
-- until a STOP condition is met
-- ============================================================
payments AS (
-- ANCHOR MEMBER
-- This is our starting point
-- The first payment date = the plan's start date
SELECT
customer_id,
plan_id,
plan_name,
price,
start_date AS payment_date,
next_plan_date
FROM customer_plans
UNION ALL
-- RECURSIVE MEMBER
-- This part references the CTE itself (payments)
-- Each iteration adds 1 month to the previous payment date
SELECT
customer_id,
plan_id,
plan_name,
price,
DATEADD(MONTH, 1, payment_date) AS payment_date,
next_plan_date
FROM payments
-- STOP CONDITIONS
-- The recursion stops when ANY of these are true:
WHERE
-- Stop for annual plans after the first payment
-- (annual customers only pay once)
plan_id != 3
-- Stop if adding 1 more month exceeds the plan end date
-- (customer changed plans or end of 2020)
AND DATEADD(MONTH, 1, payment_date) < next_plan_date
-- Stop if we've gone past December 31st 2020
AND DATEADD(MONTH, 1, payment_date) <= '2020-12-31'
)Let me walk you through exactly what this recursive CTE does using a concrete example.
Imagine a customer who starts on basic monthly on January 15th 2020 and upgrades to pro monthly on June 15th 2020. Here's how the recursion plays out:
Anchor runs first:
→ Generates: Jan 15, 2020 | basic monthly | $9.90
Recursion - Iteration 1:
→ Jan 15 + 1 month = Feb 15
→ Is Feb 15 < Jun 15? YES → keep going
→ Generates: Feb 15, 2020 | basic monthly | $9.90
Recursion - Iteration 2:
→ Feb 15 + 1 month = Mar 15
→ Is Mar 15 < Jun 15? YES → keep going
→ Generates: Mar 15, 2020 | basic monthly | $9.90
... continues for Apr, May ...
Recursion - Iteration 5:
→ May 15 + 1 month = Jun 15
→ Is Jun 15 < Jun 15? NO → STOP
→ Basic monthly payments end at May
Then the anchor runs again for the pro monthly plan,
starting from Jun 15 and generating monthly rows
through to Dec 31 2020.One very important thing to note for SQL Server specifically, recursive CTEs have a default recursion limit of 100 iterations. Since we're generating up to 12 monthly payments in a year, we're well within that. But as a best practice we add this at the end of our query:
OPTION (MAXRECURSION 365)This raises the limit to 365, which is more than enough for any monthly billing scenario within a single year.
Final SELECT - Calculating the Payment Amount
-- ============================================================
-- FINAL SELECT
-- ============================================================
-- PURPOSE:
-- Now that we have all payment dates generated, we need to
-- calculate the ACTUAL amount charged.
--
-- The key rule here is the UPGRADE DISCOUNT:
-- If a customer upgrades mid-month and already made a payment
-- that month, they only pay the DIFFERENCE between plans.
--
-- We use LAG() to look back at the previous payment
-- and check if it was in the same calendar month.
-- ============================================================
SELECT
customer_id,
plan_id,
plan_name,
payment_date,
CASE
-- Two conditions must BOTH be true for the discount:
-- 1. The previous payment was in the same month/year
-- 2. The previous payment amount was LESS than current
-- (meaning this is an upgrade, not a downgrade)
WHEN MONTH(payment_date) = MONTH(
LAG(payment_date) OVER (
PARTITION BY customer_id
ORDER BY payment_date)
)
AND YEAR(payment_date) = YEAR(
LAG(payment_date) OVER (
PARTITION BY customer_id
ORDER BY payment_date)
)
AND price > LAG(price) OVER (
PARTITION BY customer_id
ORDER BY payment_date)
-- If upgrade in same month: charge the difference only
THEN price - LAG(price) OVER (
PARTITION BY customer_id
ORDER BY payment_date)
-- Otherwise: charge the full plan price
ELSE price
END AS amount,
-- Payment order tracks which payment number this is
-- for each customer (1st payment, 2nd payment, etc.)
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY payment_date
) AS payment_order
FROM payments
ORDER BY customer_id, payment_date
OPTION (MAXRECURSION 365);The final piece of the puzzle is the upgrade discount logic, and we handle it entirely with the LAG() window function.
LAG() is the opposite of LEAD(). Instead of looking forward to the next row, it looks backward to the previous row. We use it here to check three things about the previous payment:
Was it in the same month and year? If yes, the customer already paid something this month
Was the previous price lower than the current price? If yes, this is an upgrade — not a downgrade or a renewal
If both of those conditions are true, we apply the discount by subtracting the previous payment amount from the current plan price.
Putting It All Together
Here's the complete solution in one clean block:
-- ============================================================
-- PART C: COMPLETE SOLUTION
-- Creating the 2020 Payments Table
-- ============================================================
WITH customer_plans AS (
SELECT
s.customer_id,
s.plan_id,
p.plan_name,
p.price,
s.start_date,
ISNULL(
LEAD(s.start_date) OVER (
PARTITION BY s.customer_id
ORDER BY s.start_date
),
'2020-12-31') AS next_plan_date
FROM subscriptions s
JOIN plans p ON s.plan_id = p.plan_id
WHERE s.plan_id NOT IN (0, 4)
AND YEAR(s.start_date) = 2020
),
payments AS (
SELECT
customer_id,
plan_id,
plan_name,
price,
start_date AS payment_date,
next_plan_date
FROM customer_plans
UNION ALL
SELECT
customer_id,
plan_id,
plan_name,
price,
DATEADD(MONTH, 1, payment_date),
next_plan_date
FROM payments
WHERE
plan_id != 3
AND DATEADD(MONTH, 1, payment_date) < next_plan_date
AND DATEADD(MONTH, 1, payment_date) <= '2020-12-31'
)
SELECT
customer_id,
plan_id,
plan_name,
payment_date,
CASE
WHEN MONTH(payment_date) = MONTH(LAG(payment_date) OVER (
PARTITION BY customer_id ORDER BY payment_date))
AND YEAR(payment_date) = YEAR(LAG(payment_date) OVER (
PARTITION BY customer_id ORDER BY payment_date))
AND price > LAG(price) OVER (
PARTITION BY customer_id ORDER BY payment_date)
THEN price - LAG(price) OVER (
PARTITION BY customer_id ORDER BY payment_date)
ELSE price
END AS amount,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY payment_date
) AS payment_order
FROM payments
ORDER BY customer_id, payment_date
OPTION (MAXRECURSION 365);Key Takeaways From Part C
Before we move on, let me summarise the three SQL concepts that made this solution possible:
| Concept | What It Does | Where We Used It |
|---|---|---|
| LEAD() | Looks at the next row's value | Finding when the next plan starts |
| Recursive CTE | Loops through data iteratively | Generating monthly payment rows |
| LAG() | Looks at the previous row's value | Checking for upgrade discounts |
| MAXRECURSION | Sets the recursion depth limit | Allowing up to 365 iterations |
If you take nothing else from Part C, take this: window functions are incredibly powerful for problems that involve looking at what happened before or after a given row.
LEAD and LAG in particular come up constantly in real-world SQL interviews and on the job, so make sure you're comfortable with them.
PART D — OUTSIDE THE BOX QUESTIONS
What Is Part D About?
Part D is a different kind of challenge. There are no definitive right or wrong answers here. Instead, the questions are asking you to think like a data analyst inside a real business. This is where the challenge gets really interesting. Unlike Parts A, B, and C where there were specific right and wrong answers, Part D is deliberately open-ended. These are the kinds of questions you'd be asked in a data analyst job interview or in a real business setting.
There are five questions, and we're going to work through every single one of them. Some will have SQL to back them up. Others are more strategic and conceptual, and that's intentional. Because in the real world, a great data analyst doesn't just write queries. They think critically about the business, ask the right questions, and translate data into decisions.
Let's get into it.
The questions are:
How would you calculate the rate of growth for Foodie-Fi?
What key metrics would you recommend Foodie-Fi management to track over time to assess performance of their overall business?
What are some key customer journeys or experiences that you would analyse further to improve customer retention?
If the Foodie-Fi team were to create an exit survey shown to customers who wish to cancel their subscription, what questions would you include in the survey?
What business levers could the Foodie-Fi team use to reduce the customer churn rate? How would you validate the effectiveness of your ideas?
What are the key business questions you would want the data to answer?
Let me be clear about something. These questions are actually more important than Part C in a real work setting. Writing complex SQL is valuable, but being able to identify what to measure, what questions to ask, and what insights actually matter to a business is what separates a good data analyst from a great one.
So let's work through each one.
Question 1 - Growth Rate
-- ============================================================
-- QUESTION 1: Calculating Foodie-Fi's Growth Rate
-- ============================================================
-- APPROACH:
-- We measure growth through new PAID subscriptions per month
-- Trials are excluded — they are free and don't represent
-- real business growth until they convert to paid plans
-- We then calculate month-over-month (MoM) growth rate
-- as a percentage change from the previous month
-- ============================================================
WITH monthly_signups AS (
SELECT
YEAR(start_date) AS signup_year,
MONTH(start_date) AS signup_month,
FORMAT(start_date, 'yyyy-MM') AS year_month,
COUNT(DISTINCT customer_id) AS new_customers
FROM subscriptions
-- Exclude trial (free) and churn (lost customers)
-- We only want genuine new paying subscribers
WHERE plan_id NOT IN (0, 4)
GROUP BY
YEAR(start_date),
MONTH(start_date),
FORMAT(start_date, 'yyyy-MM')
),
growth_calculation AS (
SELECT
year_month,
new_customers,
-- LAG() retrieves the previous month's customer count
-- so we can calculate the change month over month
LAG(new_customers) OVER (
ORDER BY signup_year, signup_month
) AS prev_month_customers
FROM monthly_signups
)
SELECT
year_month,
new_customers,
prev_month_customers,
-- Growth Rate Formula:
-- (Current Month - Previous Month) / Previous Month * 100
CASE
WHEN prev_month_customers IS NULL THEN NULL -- First month, no comparison
WHEN prev_month_customers = 0 THEN NULL -- Avoid divide by zero error
ELSE ROUND(
(CAST(new_customers AS FLOAT) - prev_month_customers)
/ prev_month_customers * 100,
2)
END AS mom_growth_rate_pct
FROM growth_calculation
ORDER BY year_month;Growth rate is one of those metrics that every subscription business obsesses over, and for good reason. But it's important to think carefully about how you define growth.
Growth rate is the headline number for any subscription business. But here's something worth saying out loud, a single growth metric never tells the whole story.
Customer count growth is important, but I'd always recommend tracking it alongside revenue growth. Why? Because it's entirely possible to grow your customer count while actually losing revenue, if a large number of customers are downgrading from pro plans to basic plans, for example.
So think of growth rate as the starting point of a conversation, not the end of one.
In this query, we're looking at new paid subscriptions per month and calculating the month-over-month percentage change. This is arguably the cleanest signal of business momentum because it excludes free trials. Trials are great for acquisition, but they don't pay the bills.
A positive percentage means the business is growing that month. A negative percentage is a warning sign worth investigating. And if you wanted to extend this for a real business, you'd layer in net revenue growth alongside customer count growth because it's entirely possible to grow customers while losing revenue if too many people are downgrading.
Question 2 - Key Metrics to Track
-- ============================================================
-- QUESTION 2: Key Metrics for Management
-- ============================================================
-- -------------------------------------------------------
-- METRIC 1: Monthly Recurring Revenue (MRR)
-- -------------------------------------------------------
-- MRR is the single most important metric for any
-- subscription business. It tells you how much predictable
-- revenue the business generates each month.
-- -------------------------------------------------------
SELECT
FORMAT(s.start_date, 'yyyy-MM') AS month,
COUNT(DISTINCT s.customer_id) AS active_subscribers,
SUM(p.price) AS total_mrr
FROM subscriptions s
JOIN plans p ON s.plan_id = p.plan_id
WHERE p.plan_name NOT IN ('trial', 'churn')
GROUP BY FORMAT(s.start_date, 'yyyy-MM')
ORDER BY month;
-- -------------------------------------------------------
-- METRIC 2: Monthly Churn Rate
-- -------------------------------------------------------
-- Churn rate tells you what percentage of your customers
-- are leaving each month. Even a small churn rate,
-- left unchecked, can completely undermine growth.
-- -------------------------------------------------------
WITH monthly_churn AS (
SELECT
FORMAT(start_date, 'yyyy-MM') AS churn_month,
COUNT(DISTINCT customer_id) AS churned_customers
FROM subscriptions
WHERE plan_id = 4
GROUP BY FORMAT(start_date, 'yyyy-MM')
),
monthly_active AS (
SELECT
FORMAT(start_date, 'yyyy-MM') AS active_month,
COUNT(DISTINCT customer_id) AS active_customers
FROM subscriptions
WHERE plan_id NOT IN (0, 4)
GROUP BY FORMAT(start_date, 'yyyy-MM')
)
SELECT
ma.active_month,
ma.active_customers,
ISNULL(mc.churned_customers, 0) AS churned_customers,
ROUND(
CAST(ISNULL(mc.churned_customers, 0) AS FLOAT)
/ ma.active_customers * 100,
2) AS churn_rate_pct
FROM monthly_active ma
LEFT JOIN monthly_churn mc
ON ma.active_month = mc.churn_month
ORDER BY ma.active_month;
-- -------------------------------------------------------
-- METRIC 3: Trial-to-Paid Conversion Rate
-- -------------------------------------------------------
-- Every customer starts on a free trial.
-- This metric tells us what percentage of those trial
-- users actually convert to a paying plan.
-- This is critical because it reflects how well the
-- product is demonstrating its value.
-- -------------------------------------------------------
WITH trial_customers AS (
SELECT customer_id
FROM subscriptions
WHERE plan_id = 0
),
converted_customers AS (
SELECT DISTINCT customer_id
FROM subscriptions
WHERE plan_id IN (1, 2, 3)
)
SELECT
COUNT(DISTINCT t.customer_id) AS total_trial_customers,
COUNT(DISTINCT c.customer_id) AS converted_to_paid,
COUNT(DISTINCT t.customer_id)
- COUNT(DISTINCT c.customer_id) AS not_converted,
ROUND(
CAST(COUNT(DISTINCT c.customer_id) AS FLOAT)
/ COUNT(DISTINCT t.customer_id) * 100,
2) AS conversion_rate_pct
FROM trial_customers t
LEFT JOIN converted_customers c
ON t.customer_id = c.customer_id;
-- -------------------------------------------------------
-- METRIC 4: Average Revenue Per User (ARPU)
-- -------------------------------------------------------
-- ARPU tells you how much each customer is worth
-- on average. If ARPU is growing, it means customers
-- are upgrading to higher plans. If it's shrinking,
-- customers might be downgrading.
-- -------------------------------------------------------
WITH customer_revenue AS (
SELECT
s.customer_id,
SUM(p.price) AS total_spent
FROM subscriptions s
JOIN plans p ON s.plan_id = p.plan_id
WHERE p.plan_name NOT IN ('trial', 'churn')
GROUP BY s.customer_id
)
SELECT
COUNT(customer_id) AS total_paying_customers,
SUM(total_spent) AS total_revenue,
ROUND(AVG(total_spent), 2) AS avg_revenue_per_user,
ROUND(MIN(total_spent), 2) AS min_spent,
ROUND(MAX(total_spent), 2) AS max_spent
FROM customer_revenue;
-- -------------------------------------------------------
-- METRIC 5: Plan Distribution Over Time
-- -------------------------------------------------------
-- How is the customer base spread across plans?
-- If too many customers are on the basic plan and
-- not upgrading, that's a revenue ceiling problem.
-- Tracking this monthly shows whether the product
-- is successfully moving customers up the value ladder.
-- -------------------------------------------------------
SELECT
p.plan_name,
COUNT(DISTINCT s.customer_id) AS customer_count,
ROUND(
CAST(COUNT(DISTINCT s.customer_id) AS FLOAT)
/ (SELECT COUNT(DISTINCT customer_id)
FROM subscriptions) * 100,
2) AS percentage_of_total
FROM subscriptions s
JOIN plans p ON s.plan_id = p.plan_id
GROUP BY p.plan_id, p.plan_name
ORDER BY p.plan_id;If I were presenting these metrics to Foodie-Fi's management team, I'd frame them like this:
Think of these five metrics as a funnel scorecard or a business health dashboard. At the top of the funnel you have conversion rate: are we turning trials into paying customers? In the middle you have MRR and ARPU: are those customers paying us enough? And at the bottom you have churn rate: are we keeping them?
CONVERSION RATE → Are we turning free trials into paying customers?
MRR → Is the revenue base growing?
CHURN RATE → Are we keeping the customers we have?
ARPU → Are customers moving up to higher value plans?
PLAN DISTRIBUTION→ Where is our customer base concentrated?A healthy subscription business needs all four metrics moving in the right direction. High conversion but high churn means there's a product-market fit issue. Low churn but low conversion means the trial experience might need work. These metrics tell a story together.
Question 3 - Key Customer Journeys to analyze for retention
This question is asking us to think like a retention strategist. The goal isn't just to know what customers did, it's to identify the journeys that signal risk or opportunity, and then act on them.
-- ============================================================
-- QUESTION 3: Customer Journeys to Analyse for Retention
-- ============================================================
-- -------------------------------------------------------
-- JOURNEY ANALYSIS 1: Full Subscription Path Map
-- -------------------------------------------------------
-- Map every customer's complete journey from start to finish
-- This reveals which paths lead to long-term retention
-- and which paths lead to churn
-- -------------------------------------------------------
WITH customer_journey AS (
SELECT
customer_id,
-- STRING_AGG builds a readable path for each customer
-- e.g. "trial → basic monthly → churn"
STRING_AGG(plan_name, ' → ')
WITHIN GROUP (ORDER BY start_date) AS journey_path
FROM subscriptions s
JOIN plans p ON s.plan_id = p.plan_id
GROUP BY customer_id
)
SELECT
journey_path,
COUNT(*) AS number_of_customers,
ROUND(
CAST(COUNT(*) AS FLOAT)
/ (SELECT COUNT(DISTINCT customer_id)
FROM subscriptions) * 100,
2) AS pct_of_customers
FROM customer_journey
GROUP BY journey_path
ORDER BY number_of_customers DESC;
-- -------------------------------------------------------
-- JOURNEY ANALYSIS 2: Customers Who Churned Directly
-- From Trial (Never Converted)
-- -------------------------------------------------------
-- These customers tried the product and immediately left.
-- This is the most critical retention failure point.
-- Understanding WHY they left starts here.
-- -------------------------------------------------------
WITH trial_then_churn AS (
SELECT
s.customer_id,
s.start_date AS trial_start,
-- Get the very next plan after trial
LEAD(s.plan_id) OVER (
PARTITION BY s.customer_id
ORDER BY s.start_date
) AS next_plan_id,
LEAD(s.start_date) OVER (
PARTITION BY s.customer_id
ORDER BY s.start_date
) AS next_plan_date
FROM subscriptions s
WHERE s.plan_id = 0 -- Trial only
)
SELECT
COUNT(*) AS trial_to_churn_customers,
-- How many days did they stay in trial before churning?
ROUND(AVG(CAST(
DATEDIFF(DAY, trial_start, next_plan_date)
AS FLOAT)), 1) AS avg_days_before_churn,
ROUND(
CAST(COUNT(*) AS FLOAT)
/ (SELECT COUNT(DISTINCT customer_id)
FROM subscriptions) * 100,
2) AS pct_of_all_customers
FROM trial_then_churn
-- Only keep rows where next plan was churn (plan_id = 4)
WHERE next_plan_id = 4;
-- -------------------------------------------------------
-- JOURNEY ANALYSIS 3: Customers Who Downgraded
-- -------------------------------------------------------
-- Downgrades are an early warning sign of churn.
-- A customer going from pro monthly back to basic monthly
-- is telling you the higher plan isn't worth it to them.
-- These customers need targeted retention efforts NOW
-- before they churn completely.
-- -------------------------------------------------------
WITH plan_changes AS (
SELECT
s.customer_id,
s.plan_id AS current_plan,
p.plan_name AS current_plan_name,
p.price AS current_price,
LAG(s.plan_id) OVER (
PARTITION BY s.customer_id
ORDER BY s.start_date
) AS previous_plan,
LAG(p.price) OVER (
PARTITION BY s.customer_id
ORDER BY s.start_date
) AS previous_price,
s.start_date AS change_date
FROM subscriptions s
JOIN plans p ON s.plan_id = p.plan_id
WHERE s.plan_id NOT IN (0, 4) -- Exclude trial and churn
)
SELECT
previous_plan,
current_plan,
current_plan_name,
COUNT(DISTINCT customer_id) AS customers_who_downgraded,
-- Average price drop per downgrade
ROUND(AVG(previous_price - current_price), 2)
AS avg_price_drop,
-- Total monthly revenue impact of these downgrades
ROUND(SUM(previous_price - current_price), 2)
AS total_monthly_revenue_impact
FROM plan_changes
-- A downgrade = moving to a CHEAPER plan (lower price)
WHERE previous_price > current_price
GROUP BY previous_plan, current_plan, current_plan_name
ORDER BY customers_who_downgraded DESC;
-- -------------------------------------------------------
-- JOURNEY ANALYSIS 4: How Long Before Customers Upgrade?
-- -------------------------------------------------------
-- For customers who DID upgrade, how long did it take?
-- If the average is 3 months, maybe we nudge customers
-- at the 2-month mark with a targeted upgrade offer.
-- -------------------------------------------------------
WITH upgrades AS (
SELECT
s.customer_id,
s.plan_id AS new_plan_id,
p.plan_name AS new_plan_name,
LAG(s.plan_id) OVER (
PARTITION BY s.customer_id
ORDER BY s.start_date
) AS old_plan_id,
LAG(s.start_date) OVER (
PARTITION BY s.customer_id
ORDER BY s.start_date
) AS old_plan_start,
s.start_date AS upgrade_date
FROM subscriptions s
JOIN plans p ON s.plan_id = p.plan_id
)
SELECT
new_plan_name AS upgraded_to,
COUNT(DISTINCT customer_id) AS total_upgrades,
-- Average days spent on previous plan before upgrading
ROUND(AVG(CAST(
DATEDIFF(DAY, old_plan_start, upgrade_date)
AS FLOAT)), 0) AS avg_days_before_upgrade,
ROUND(AVG(CAST(
DATEDIFF(DAY, old_plan_start, upgrade_date)
AS FLOAT)) / 30, 1) AS avg_months_before_upgrade
FROM upgrades
-- Only keep actual upgrades (moving to a higher plan)
WHERE new_plan_id > old_plan_id
AND old_plan_id IS NOT NULL
AND old_plan_id != 0 -- Not from trial
GROUP BY new_plan_id, new_plan_name
ORDER BY new_plan_id;
-- -------------------------------------------------------
-- JOURNEY ANALYSIS 5: The "At Risk" Customer Segment
-- -------------------------------------------------------
-- Customers on basic monthly for more than 3 months
-- without upgrading are potentially "stuck" customers.
-- They haven't churned yet but they haven't seen enough
-- value to upgrade. These need targeted intervention.
-- -------------------------------------------------------
WITH basic_monthly_duration AS (
SELECT
s.customer_id,
s.start_date AS basic_start_date,
-- Next plan start date (or today if still on basic)
ISNULL(
LEAD(s.start_date) OVER (
PARTITION BY s.customer_id
ORDER BY s.start_date
),
GETDATE()) AS next_plan_date,
LEAD(s.plan_id) OVER (
PARTITION BY s.customer_id
ORDER BY s.start_date
) AS next_plan_id
FROM subscriptions s
WHERE s.plan_id = 1 -- Basic monthly only
)
SELECT
customer_id,
basic_start_date,
DATEDIFF(MONTH, basic_start_date, next_plan_date)
AS months_on_basic,
-- Label what happened next to this customer
CASE
WHEN next_plan_id = 4 THEN 'Churned'
WHEN next_plan_id IN (2, 3) THEN 'Upgraded'
WHEN next_plan_id IS NULL THEN 'Still on Basic'
ELSE 'Other'
END AS outcome
FROM basic_monthly_duration
-- Focus on customers who stayed 3+ months on basic
WHERE DATEDIFF(MONTH, basic_start_date, next_plan_date) >= 3
ORDER BY months_on_basic DESC;The key insight from all of these journey analyses comes down to one thing, identifying the moments where customers are most at risk.
The three highest-risk moments in Foodie-Fi's customer lifecycle are:
RISK POINT 1: End of free trial
→ Did they convert or go straight to churn?
→ If they churned, why? What was missing?
RISK POINT 2: 3+ months on basic monthly without upgrading
→ These customers are engaged enough to stay
→ But not convinced enough to pay more
→ They need a targeted nudge
RISK POINT 3: After a downgrade
→ A downgrade is almost always a precursor to churn
→ These customers need immediate attentionQuestion 4 - Exit Survey Questions
This question doesn't require SQL, it requires empathy and business thinking. An exit survey is shown to customers who are about to cancel. The goal is to understand why they're leaving so the business can fix the root cause.
Here's the survey I'd recommend, broken into sections:
Section 1 - Primary Reason for Cancelling
Single choice — pick the most important one
Why are you cancelling your Foodie-Fi subscription today?
○ It's too expensive for what I get
○ I don't watch enough content to justify the cost
○ I found a better alternative (another platform)
○ The content I wanted wasn't available
○ I'm having technical issues with the platform
○ I only needed it for a specific show or season
○ I'm taking a temporary break (financial reasons)
○ The content quality wasn't what I expected
○ Other (please specify): _______________Why this matters: This single question is the most important one on the entire survey. It immediately segments churned customers into addressable categories. "Too expensive" = pricing problem. "Better alternative" = competitive problem. "Content not available" = content acquisition problem. Each answer points to a different business lever.
Section 2 - Content & Value
Scale of 1–5
Please rate your experience with Foodie-Fi:
How satisfied were you with the variety of content?
⭐ 1 — Very Dissatisfied → ⭐ 5 — Very Satisfied
How satisfied were you with the quality of content?
⭐ 1 — Very Dissatisfied → ⭐ 5 — Very Satisfied
How well did Foodie-Fi recommend content you enjoyed?
⭐ 1 — Very Poorly → ⭐ 5 — Very Well
Did you feel you got good value for the price you paid?
⭐ 1 — Very Poor Value → ⭐ 5 — Excellent ValueWhy this matters: These ratings help the team identify whether churn is driven by content gaps, recommendation engine failures, or pure pricing sensitivity. Low scores on variety but high scores on quality suggest a content library expansion problem, not a quality problem.
Section 3 - Engagement & Usage
Multiple choice
How often were you using Foodie-Fi before cancelling?
○ Daily
○ A few times a week
○ Once a week
○ A few times a month
○ Rarely — less than once a month
Which type of content did you watch most on Foodie-Fi?
(Select all that apply)
☑ Cooking tutorials and how-to videos
☑ Chef documentaries and profiles
☑ Restaurant and travel food shows
☑ Competition cooking shows
☑ Recipe-based content
☑ I didn't watch much contentWhy this matters: Usage frequency is a leading indicator of churn. Customers who are barely using the product before cancelling are giving the platform fair warning. Low usage churners are very different from high usage churners. A high-usage customer who churns means the product failed them despite engagement. A low-usage customer who churns means the product never captured them in the first place.
Section 4 - Competitive Landscape
Multiple choice
Are you switching to another food or cooking platform?
○ Yes — Netflix food content
○ Yes — YouTube cooking channels
○ Yes — Another dedicated food streaming service
○ No — I'm just taking a break from streaming
○ No — I'm cutting back on all subscriptions
What would have convinced you to stay?
○ Lower price or a more affordable plan tier
○ More content in the categories I enjoy
○ Better personalised recommendations
○ A pause option instead of full cancellation
○ Family or group sharing plan
○ Offline viewing capability
○ Nothing — my needs have genuinely changedWhy this matters: The competitive question tells the business who they're really fighting against. If most customers are leaving for YouTube, that's a pricing problem. YouTube is free. If they're leaving for Netflix, that's a content breadth problem. The "what would have convinced you to stay" question is pure gold for the product team.
Section 5 - Open Feedback & Return Intent
Free text + single choice
Is there anything specific that Foodie-Fi could have done
to keep you as a customer?
[ ]
[ Free text response box ]
[ ]
How likely are you to recommend Foodie-Fi to a friend
or family member, even though you're cancelling?
○ Very likely — I still think it's a great product
○ Somewhat likely — It has its strengths
○ Unlikely — I wouldn't recommend it currently
○ Definitely not — I was disappointed
Would you consider coming back to Foodie-Fi in the future?
○ Yes — definitely, I'm just taking a break
○ Maybe — if the content or pricing improved
○ Unlikely — my streaming needs have changed
○ No — I've found a better alternativeWhy this matters: The free text question often surfaces the most actionable insights. Things the business never thought to ask about. The "would you come back" question is also critically important because it segments churned customers into winnable and unwinnable groups. Customers who say "yes, I'm just taking a break" should be targeted with a win-back campaign at 30, 60, and 90 days after cancellation.
How to Use the Survey Data
Once the survey data is collected, here's how you'd analyse it in SQL:
-- ============================================================
-- Hypothetical Survey Analysis
-- (Once survey data is collected and loaded into SQL Server)
-- ============================================================
-- Assuming a survey_responses table exists with:
-- customer_id, cancellation_reason, value_rating,
-- content_rating, usage_frequency, would_return,
-- survey_date
-- -------------------------------------------------------
-- Top cancellation reasons by volume
-- -------------------------------------------------------
SELECT
cancellation_reason,
COUNT(*) AS response_count,
ROUND(
CAST(COUNT(*) AS FLOAT)
/ SUM(COUNT(*)) OVER () * 100,
1) AS pct_of_responses
FROM survey_responses
GROUP BY cancellation_reason
ORDER BY response_count DESC;
-- -------------------------------------------------------
-- Which plan did customers come from?
-- Does churn reason vary by plan?
-- -------------------------------------------------------
SELECT
p.plan_name,
sr.cancellation_reason,
COUNT(*) AS response_count
FROM survey_responses sr
JOIN subscriptions s
ON sr.customer_id = s.customer_id
JOIN plans p
ON s.plan_id = p.plan_id
WHERE s.plan_id != 0 -- Exclude trial
GROUP BY p.plan_name, sr.cancellation_reason
ORDER BY p.plan_name, response_count DESC;
-- -------------------------------------------------------
-- Win-back target list
-- Customers who said they'd consider returning
-- -------------------------------------------------------
SELECT
sr.customer_id,
sr.cancellation_reason,
sr.would_return,
sr.survey_date,
-- Days since they cancelled
DATEDIFF(DAY, sr.survey_date, GETDATE()) AS days_since_cancelled
FROM survey_responses sr
WHERE sr.would_return IN ('Yes - definitely', 'Maybe')
ORDER BY days_since_cancelled ASC;Question 5 - Business Levers to Reduce Churn + Validation
This is the most strategic question of all five. It's asking two things: what can we do, and how do we know if it worked?
-- ============================================================
-- QUESTION 5: Understanding Churn Patterns to Inform Strategy
-- ============================================================
-- -------------------------------------------------------
-- ANALYSIS 1: Revenue impact of churn
-- -------------------------------------------------------
-- Before proposing solutions, quantify the problem.
-- This is what gets leadership to act — a dollar figure.
-- -------------------------------------------------------
WITH churned_customers AS (
SELECT
s.customer_id,
s.start_date AS churn_date,
LAG(s.plan_id) OVER (
PARTITION BY s.customer_id
ORDER BY s.start_date
) AS last_plan_id
FROM subscriptions s
WHERE s.plan_id = 4
)
SELECT
p.plan_name AS last_plan_before_churn,
COUNT(cc.customer_id) AS customers_churned,
SUM(p.price) AS monthly_revenue_lost,
-- Annualise the impact to make it real for leadership
SUM(p.price) * 12 AS estimated_annual_revenue_lost
FROM churned_customers cc
JOIN plans p ON cc.last_plan_id = p.plan_id
GROUP BY p.plan_id, p.plan_name
ORDER BY monthly_revenue_lost DESC;
-- -------------------------------------------------------
-- ANALYSIS 2: Churn timing — when in the lifecycle
-- do customers most commonly leave?
-- -------------------------------------------------------
-- This tells us WHERE to focus retention interventions
-- -------------------------------------------------------
WITH customer_lifespan AS (
SELECT
s.customer_id,
MIN(s.start_date) AS first_date,
MAX(s.start_date) AS churn_date,
DATEDIFF(DAY,
MIN(s.start_date),
MAX(s.start_date)
) AS days_as_customer
FROM subscriptions s
GROUP BY s.customer_id
-- Only include customers who actually churned
HAVING MAX(s.plan_id) = 4
)
SELECT
-- Bucket customers by how long they stayed
CASE
WHEN days_as_customer <= 7 THEN '0-7 days (Trial period)'
WHEN days_as_customer <= 30 THEN '8-30 days (First month)'
WHEN days_as_customer <= 90 THEN '31-90 days (First quarter)'
WHEN days_as_customer <= 180 THEN '91-180 days (6 months)'
ELSE '180+ days (Long term)'
END AS churn_bucket,
COUNT(*) AS customers_churned,
ROUND(AVG(CAST(days_as_customer AS FLOAT)), 0)
AS avg_days_as_customer
FROM customer_lifespan
GROUP BY
CASE
WHEN days_as_customer <= 7 THEN '0-7 days (Trial period)'
WHEN days_as_customer <= 30 THEN '8-30 days (First month)'
WHEN days_as_customer <= 90 THEN '31-90 days (First quarter)'
WHEN days_as_customer <= 180 THEN '91-180 days (6 months)'
ELSE '180+ days (Long term)'
END
ORDER BY MIN(days_as_customer);
-- -------------------------------------------------------
-- ANALYSIS 3: Seasonality of churn
-- -------------------------------------------------------
-- Are there specific months where churn spikes?
-- This helps time retention campaigns proactively
-- -------------------------------------------------------
SELECT
MONTH(start_date) AS churn_month_number,
DATENAME(MONTH, start_date) AS churn_month_name,
COUNT(DISTINCT customer_id) AS customers_churned,
RANK() OVER (
ORDER BY COUNT(DISTINCT customer_id) DESC
) AS churn_rank
FROM subscriptions
WHERE plan_id = 4
GROUP BY
MONTH(start_date),
DATENAME(MONTH, start_date)
ORDER BY churn_month_number;Now let's talk about the actual business levers, the things Foodie-Fi could do to reduce churn and critically, how we'd validate whether they work.
Lever 1 - Improve the Trial-to-Paid Experience
The Problem: Customers who churn during or immediately after their trial never found enough value to justify paying. This is the highest-volume churn point.
What To Do:
Send a personalised email on Day 5 of the trial (2 days before it ends) highlighting content based on what they actually watched
Add an in-app prompt at the end of the trial showing a curated "watch list" to draw them in
Offer a discounted first month for customers who are about to let their trial expire without converting
How To Validate It:
-- Run an A/B test:
-- Group A (Control) = trial ends with no intervention
-- Group B (Treatment) = trial ends with personalised email
-- After 90 days, compare conversion rates:
SELECT
test_group,
COUNT(*) AS total_trial_customers,
SUM(CASE WHEN converted = 1 THEN 1 ELSE 0 END)
AS converted_customers,
ROUND(
CAST(SUM(CASE WHEN converted = 1 THEN 1 ELSE 0 END)
AS FLOAT) / COUNT(*) * 100,
2) AS conversion_rate_pct
FROM ab_test_results
WHERE test_name = 'trial_end_email'
GROUP BY test_group;
-- A statistically significant improvement in Group B's
-- conversion rate validates the interventionLever 2 - Introduce a Pause Option
The Problem: Some customers cancel not because they hate the product, but because of temporary financial pressure or reduced viewing time. Right now, cancellation is their only option.
What To Do:
Introduce a "Pause Subscription" feature. It allow customers to pause for 1 or 3 months before the cancellation confirmation screen
This should be shown as an alternative to churning during the cancellation flow
How To Validate It:
-- Track pause conversion rate:
-- Of customers who reached the cancellation screen,
-- what % chose to pause instead of cancel?
-- After 6 months, compare outcomes:
-- Did paused customers return at a higher rate
-- than customers who fully cancelled?
SELECT
action_taken, -- 'paused' vs 'cancelled'
COUNT(*) AS total_customers,
SUM(CASE WHEN returned_within_90_days = 1
THEN 1 ELSE 0 END) AS returned_customers,
ROUND(
CAST(SUM(CASE WHEN returned_within_90_days = 1
THEN 1 ELSE 0 END) AS FLOAT)
/ COUNT(*) * 100,
2) AS return_rate_pct
FROM cancellation_flow_results
GROUP BY action_taken;Lever 3 - Targeted Retention Campaign for At-Risk Customers
The Problem: Customers who have been on basic monthly for 3+ months without upgrading are showing low engagement signals. They're not getting enough value to move up, but they haven't left yet.
What To Do:
Identify this segment monthly using the "at-risk" query from Question 3
Send them a targeted offer, for example, one month of pro monthly at the basic monthly price
The goal is to get them to experience the pro plan so they see the value and convert permanently
How To Validate It:
-- Measure upgrade rate for customers who received the offer
-- versus a control group who did not
SELECT
offer_group, -- 'received_offer' vs 'no_offer'
COUNT(*) AS at_risk_customers,
SUM(CASE WHEN upgraded = 1 THEN 1 ELSE 0 END)
AS upgraded_customers,
SUM(CASE WHEN churned_within_60_days = 1
THEN 1 ELSE 0 END)
AS churned_customers,
ROUND(
CAST(SUM(CASE WHEN upgraded = 1 THEN 1 ELSE 0 END)
AS FLOAT) / COUNT(*) * 100,
2) AS upgrade_rate_pct,
ROUND(
CAST(SUM(CASE WHEN churned_within_60_days = 1
THEN 1 ELSE 0 END) AS FLOAT)
/ COUNT(*) * 100,
2) AS churn_rate_pct
FROM retention_campaign_results
WHERE campaign_name = 'basic_monthly_upgrade_offer'
GROUP BY offer_group;Lever 4 - Annual Plan Incentive
The Problem: Monthly customers are inherently higher churn risk than annual customers because the decision to stay or leave comes up every single month. Annual plan customers have already committed for a year.
What To Do:
Offer monthly customers a compelling discount to switch to annual, for example, "Get 2 months free when you switch to annual"
Target this specifically at customers who have been on monthly plans for 6+ months without churning, these are your most loyal monthly customers and the most likely to respond to an annual offer
How To Validate It:
-- Compare 12-month retention rates:
-- Annual plan customers vs monthly plan customers
-- who were offered but declined the annual upgrade
SELECT
plan_type,
COUNT(*) AS total_customers,
-- Still active 12 months later?
SUM(CASE WHEN active_after_12_months = 1
THEN 1 ELSE 0 END) AS retained_at_12_months,
ROUND(
CAST(SUM(CASE WHEN active_after_12_months = 1
THEN 1 ELSE 0 END) AS FLOAT)
/ COUNT(*) * 100,
2) AS retention_rate_pct,
-- Revenue generated over 12 months
ROUND(AVG(revenue_12_months), 2)
AS avg_revenue_per_customer
FROM plan_retention_comparison
GROUP BY plan_type;The Validation Framework
Regardless of which lever you pull, every retention initiative should follow the same validation framework:
STEP 1: BASELINE
→ Measure current churn rate BEFORE any intervention
→ Document it. This is your control number.
STEP 2: A/B TEST
→ Split customers randomly into two groups
→ Group A (Control) = no change
→ Group B (Treatment) = receives the intervention
→ Run for a minimum of 60-90 days
STEP 3: MEASURE
→ Compare churn rate between groups
→ Compare upgrade rate between groups
→ Calculate revenue impact
STEP 4: STATISTICAL SIGNIFICANCE
→ Make sure the difference isn't just random chance
→ Use a chi-square test for conversion rate comparisons
→ Need at least 95% confidence before declaring success
STEP 5: ROLL OUT OR REJECT
→ If statistically significant improvement → roll out to all customers
→ If no improvement → learn from it and test a new hypothesis
→ Document everything either wayQuestion 6 - Key Business Questions
This final section is where we go beyond the data and think strategically. Here are four business questions I'd want answered, with the SQL to answer them:
-- ============================================================
-- BUSINESS QUESTION 1:
-- What is the most common subscription journey customers take?
-- ============================================================
-- WHY THIS MATTERS:
-- If we know the most common path customers take, we can
-- optimise for it. If most customers go Trial → Basic → Churn,
-- that tells us something very different than
-- Trial → Basic → Pro Annual.
-- ============================================================
WITH customer_journey AS (
SELECT
customer_id,
-- STRING_AGG concatenates all plan names in order
-- giving us a full picture of each customer's journey
STRING_AGG(plan_name, ' → ')
WITHIN GROUP (ORDER BY start_date) AS journey_path
FROM subscriptions s
JOIN plans p ON s.plan_id = p.plan_id
GROUP BY customer_id
)
SELECT
journey_path,
COUNT(*) AS number_of_customers,
ROUND(
CAST(COUNT(*) AS FLOAT)
/ (SELECT COUNT(DISTINCT customer_id)
FROM subscriptions) * 100,
2) AS percentage_of_customers
FROM customer_journey
GROUP BY journey_path
ORDER BY number_of_customers DESC;
-- ============================================================
-- BUSINESS QUESTION 2:
-- How long do customers stay on each plan before leaving?
-- ============================================================
-- WHY THIS MATTERS:
-- If customers on the basic plan only stay for 2 months
-- before churning, that tells us the basic plan might not
-- be sticky enough. If pro annual customers stay for years,
-- that tells us where to focus retention efforts.
-- ============================================================
WITH plan_duration AS (
SELECT
s.customer_id,
s.plan_id,
p.plan_name,
s.start_date,
LEAD(s.start_date) OVER (
PARTITION BY s.customer_id
ORDER BY s.start_date
) AS end_date
FROM subscriptions s
JOIN plans p ON s.plan_id = p.plan_id
)
SELECT
plan_name,
COUNT(*) AS total_customers,
ROUND(AVG(CAST(
DATEDIFF(DAY, start_date, end_date)
AS FLOAT)), 1) AS avg_days_on_plan,
ROUND(AVG(CAST(
DATEDIFF(DAY, start_date, end_date)
AS FLOAT)) / 7, 1) AS avg_weeks_on_plan
FROM plan_duration
WHERE end_date IS NOT NULL
AND plan_id NOT IN (0, 4)
GROUP BY plan_id, plan_name
ORDER BY plan_id;
-- ============================================================
-- BUSINESS QUESTION 3:
-- Which months see the highest churn? (Seasonality)
-- ============================================================
-- WHY THIS MATTERS:
-- If churn spikes in January every year, maybe customers
-- are cancelling New Year subscriptions. If it spikes in
-- summer, maybe engagement drops when people are on holiday.
-- Knowing WHEN churn happens helps target retention campaigns.
-- ============================================================
SELECT
MONTH(start_date) AS churn_month_number,
DATENAME(MONTH, start_date) AS churn_month_name,
COUNT(DISTINCT customer_id) AS customers_churned,
RANK() OVER (
ORDER BY COUNT(DISTINCT customer_id) DESC
) AS churn_rank
FROM subscriptions
WHERE plan_id = 4
GROUP BY
MONTH(start_date),
DATENAME(MONTH, start_date)
ORDER BY churn_month_number;
-- ============================================================
-- BUSINESS QUESTION 4:
-- How much revenue is churn costing the business?
-- ============================================================
-- WHY THIS MATTERS:
-- Churn isn't just a customer count problem — it's a
-- revenue problem. This query puts a dollar value on
-- churn, which is the kind of number that gets
-- management's attention immediately.
-- ============================================================
WITH churned_customers AS (
SELECT
s.customer_id,
s.start_date AS churn_date,
-- Get the plan they were on BEFORE churning
LAG(s.plan_id) OVER (
PARTITION BY s.customer_id
ORDER BY s.start_date
) AS last_plan_id
FROM subscriptions s
WHERE s.plan_id = 4
)
SELECT
p.plan_name AS last_plan_before_churn,
COUNT(cc.customer_id) AS customers_churned,
SUM(p.price) AS monthly_revenue_lost,
SUM(p.price) * 12 AS estimated_annual_revenue_lost
FROM churned_customers cc
JOIN plans p ON cc.last_plan_id = p.plan_id
GROUP BY p.plan_id, p.plan_name
ORDER BY monthly_revenue_lost DESC;Key Takeaways From Part D
The business questions in Part D teach us something that no amount of SQL syntax can, context matters. The queries themselves are not particularly complex. What's complex is knowing which questions are worth asking in the first place.
Part D is the section that separates candidates who can write SQL from candidates who can think analytically about a real business. Let me leave you with the core message from each question:
| Question | Core Message |
|---|---|
| Q1 — Growth Rate | Measure growth in both customers AND revenue. One without the other is incomplete. |
| Q2 — Key Metrics | Track MRR, churn rate, conversion rate, ARPU, and plan distribution as a connected set of vital signs. |
| Q3 — Customer Journeys | The three riskiest moments are end of trial, 3+ months on basic, and after a downgrade. |
| Q4 — Exit Survey | Ask why they left, what they'd change, and whether they'd come back. The answers point to solvable problems. |
| Q5 — Reduce Churn | Every initiative needs an A/B test and a statistical significance check. Good ideas without validation are just guesses. |
The thread that connects all five questions is this, data only has value when it drives action. The queries, the metrics, the surveys, they're all just tools. What matters is what the business does with the information.
Here's a framework I use whenever I'm approaching a new business analytics problem:
Ask yourself these 4 questions:
1. ACQUISITION → Are we getting new customers?
(Conversion rate, MRR growth)
2. RETENTION → Are we keeping existing customers?
(Churn rate, plan duration)
3. REVENUE → Are customers spending more over time?
(ARPU, upgrade rates)
4. BEHAVIOUR → How are customers actually using the product?
(Journey paths, seasonality)Every business is different, but almost every analytics problem fits somewhere into that framework. When you walk into an interview and someone asks you "what metrics would you track?", that framework will never let you down.
WRAPPING UP
Alright, that's Parts C and D of Foodie-Fi fully covered.
Let's do a quick recap of everything we covered today.
In Part C, we built a complete payment system from scratch using just SQL. We used LEAD() to identify billing windows, a recursive CTE to generate monthly payment rows, which is honestly one of the most useful patterns you'll ever learn in SQL and LAG() to handle upgrade discounts.
The key insight was breaking the problem into small, logical stages rather than trying to solve everything in one go.
In Part D, we shifted gears from technical SQL to business thinking. We built queries to calculate growth rate, MRR, churn rate, conversion rate, and ARPU, and we answered four strategic business questions about customer journeys, plan retention, seasonality, and the true cost of churn.
If you made it this far, genuinely well done. These are not beginner concepts. The fact that you're working through case studies like this puts you well ahead of the curve.
What's Next?
If you want to keep practising, I'd recommend heading over to the 8 Week SQL Challenge website. The link is in the description and working through the remaining case studies. Each one introduces different real-world scenarios and SQL patterns.
And if you found this video useful, please give it a like, it really does help the channel. Subscribe if you haven't already, and drop any questions in the comments below. I read every single one : )
I'll see you in the next one.