Answering the customer-lifecycle questions of a subscription streaming startup using MySQL — Danny Ma's #8WeekSQLChallenge, Case Study #3.
Foodie-Fi streams only food-related content — "Netflix, but exclusively cooking shows." Using nothing but their subscription data, this project answers the questions a real growth/analytics team asks: How many customers do we have? How many churn, and when? What does a typical upgrade/downgrade journey look like — and what is it worth?
- Turning business questions into SQL — churn %, trial-to-paid conversion, plan migration, revenue
- Window functions, self-joins, CTEs, and date logic to reconstruct each customer's journey
- Reasoning over a relational schema (ERD below)
| Table | Description |
|---|---|
plans |
Plan catalog — trial, basic monthly, pro monthly, pro annual, churn, with prices |
subscriptions |
Every plan-change event per customer, with start_date |
Full solutions in Foodie_Sql_querries.sql:
- How many customers has Foodie-Fi ever had?
- Monthly distribution of trial-plan starts
- Plan-event breakdown for start dates after 2020
- Customer count & % who have churned (rounded to 1 dp)
- % who churned immediately after the free trial
- Customer journeys, upgrades/downgrades, and revenue analysis
💡 Example insight: the queries compute the exact churn rate and the share of customers who leave straight after the trial — the two metrics a subscription business steers on first.
SOURCE "Foodie-Fi Schema.sql"; -- create + populate the database
SOURCE "Foodie_Sql_querries.sql"; -- run the analysis queriesTested on MySQL 8+ (Workbench, DBeaver, or any MySQL client).
| File | Purpose |
|---|---|
Foodie-Fi Schema.sql |
Table definitions + seed data |
Foodie_Sql_querries.sql |
All analysis queries, commented per question |
ERD.jpg |
Entity-relationship diagram |
Case study by Danny Ma — #8WeekSQLChallenge. Solutions authored by Sadiq Mansoor.