Hello everyone! Excited to share my SQL project, where I analyzed Foodie-Fi, a streaming service for food-related content 🍕📺
📝 Business Context: Foodie-Fi offers monthly & annual subscriptions for premium cooking content.
📌 Objective: CEO Danny aims to make data-driven decisions to analyze growth & improve business strategies.
📌 Dataset Source: This dataset is part of Danny Ma’s 8-Week SQL Challenge.
📌SQL Solutions & Analysis: Created by Me
If you find this project helpful, please ⭐ star the repo or mention my work when using it.
Feel free to fork and modify, but kindly give credit. Thank you! 😊
The analysis utilises two datasets:
Table 1: plans
🔹The plans table includes :- plan_id,plan_name, price
🔹It defines plan options and pricing structure
🔹Customers can choose which plans to join Foodie-Fi when they first sign up.
🔹Basic plan customers have limited access and can only stream their videos and is only available monthly at $9.90
🔹Pro plan customers have no watch time limits and are able to download videos for offline viewing. Pro plans start at $19.90 a month or $199 for an annual subscription.
🔹Customers can sign up to an initial 7 day free trial will automatically continue with the pro monthly subscription plan unless they cancel, downgrade to basic or upgrade to an annual pro plan at any point during the trial.
🔹When customers cancel their Foodie-Fi service - they will have a churn plan record with a null price but their plan will continue until the end of the billing period.
Table 2: subscriptions
🔹The subscriptions table includes :- customer_id, plan_id , start_date.
🔹It tracks customer actions, upgrades,downgrades, cancellations, and billing periods)
🔹Customer subscriptions show the exact date where their specific plan_id starts.
🔹If customers downgrade from a pro plan or cancel their subscription - the higher plan will remain in place until the period is over - the start_date in the subscriptions table will reflect the date that the actual plan changes.
🔹When customers upgrade their account from a basic plan to a pro or annual pro plan - the higher plan will take effect straightaway.
🔹When customers churn - they will keep their access until the end of their current billing period but the start_date will be technically the day they decided to cancel their service.
🔹 Basic Queries: SELECT, DISTINCT, WHERE, GROUP BY, ORDER BY
🔹 Aggregations: SUM(), COUNT(), AVG(), MIN(), MAX()
🔹 Functions: CAST(), ROUND(), CASE-WHEN-THEN
🔹 Joins & Subqueries
🔹 CTEs (Common Table Expressions)
🔹 Window Functions: ROW_NUMBER(), OVER(), PARTITION BY, LAG(), LEAD()
Part A. Customer Journey - View Solution Here
Q-Based off the 8 sample customers provided in the sample from the subscriptions table, write a brief description about each customer’s onboarding journey.
Part B - Data Analysis Questions - View Solution Here
Q-1 How many customers has Foodie-Fi ever had?
Q-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
Q-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
Q-4 What is the customer count and percentage of customers who have churned rounded to 1 decimal place?
Q-5 How many customers have churned straight after their initial free trial - what percentage is this rounded to the nearest whole number?
Q-6 What is the number and percentage of customer plans after their initial free trial?
Q-7 What is the customer count and percentage breakdown of all 5 plan_name values at 2020-12-31?
Q-8 How many customers have upgraded to an annual plan in 2020?
Q-9 How many days on average does it take for a customer to an annual plan from the day they join Foodie-Fi?
Q-10 Can you further breakdown this average value into 30 day periods (i.e. 0-30 days, 31-60 days etc)
Q-11 How many customers downgraded from a pro monthly to a basic monthly plan in 2020?
Part C - Outside the Box Questions View Solution Here
Q-1 How would you calculate the rate of growth for Foodie-Fi?
📧 Email: shvetamaini6@gmail.com