SQL Programming Resources on Telegram: “🚀 SQL Project Series #32 SaaS Subscription & Re…”

🚀 SQL Project Series #32

SaaS Subscription & Revenue Analytics 💻💰

Analyze subscriptions, plans, payments, upgrades, downgrades, and churn to understand how a SaaS business grows revenue and retains customers.

🎯 Business Objectives

✅ Analyze subscription growth

✅ Calculate MRR and ARR

✅ Track upgrades and downgrades

✅ Measure customer churn

✅ Analyze revenue by plan

✅ Calculate ARPU and CLV

✅ Identify high-value customers

✅ Measure monthly revenue growth

📂 Step 1: Create Database

CREATE DATABASE saas_revenue_db;
USE saas_revenue_db;

📂 Step 2: Create Customers Table

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    company_name VARCHAR(100),
    signup_date DATE,
    country VARCHAR(50)
);

📂 Step 3: Create Plans Table

CREATE TABLE plans (
    plan_id INT PRIMARY KEY,
    plan_name VARCHAR(50),
    monthly_price DECIMAL(10,2)
);

📂 Step 4: Create Subscriptions Table

CREATE TABLE subscriptions (
    subscription_id INT PRIMARY KEY,
    customer_id INT,
    plan_id INT,
    start_date DATE,
    end_date DATE,
    subscription_status VARCHAR(20),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (plan_id) REFERENCES plans(plan_id)
);

📂 Step 5: Create Subscription Changes Table

CREATE TABLE subscription_changes (
    change_id INT PRIMARY KEY,
    subscription_id INT,
    change_date DATE,
    old_plan_id INT,
    new_plan_id INT,
    change_type VARCHAR(20),
    FOREIGN KEY (subscription_id) REFERENCES subscriptions(subscription_id),
    FOREIGN KEY (old_plan_id) REFERENCES plans(plan_id),
    FOREIGN KEY (new_plan_id) REFERENCES plans(plan_id)
);

📂 Step 6: Create Payments Table

CREATE TABLE payments (
    payment_id INT PRIMARY KEY,
    subscription_id INT,
    payment_date DATE,
    amount DECIMAL(10,2),
    payment_status VARCHAR(20),
    FOREIGN KEY (subscription_id) REFERENCES subscriptions(subscription_id)
);

📂 Step 7: Insert Sample Customers

INSERT INTO customers VALUES
(1,'Rahul Sharma','DataPro','2025-01-05','India'),
(2,'Priya Verma','TechLabs','2025-01-10','India'),
(3,'Amit Patel','FinTech Solutions','2025-01-15','India'),
(4,'Sneha Joshi','CloudWorks','2025-02-01','India'),
(5,'Rohan Gupta','Analytics Hub','2025-02-10','India');

📂 Step 8: Insert Sample Plans

INSERT INTO plans VALUES
(101,'Basic',499),
(102,'Professional',999),
(103,'Business',2499),
(104,'Enterprise',4999);

📂 Step 9: Insert Sample Subscriptions

INSERT INTO subscriptions VALUES
(1001,1,102,'2025-01-05',NULL,'Active'),
(1002,2,101,'2025-01-10','2025-04-10','Cancelled'),
(1003,3,104,'2025-01-15',NULL,'Active'),
(1004,4,103,'2025-02-01',NULL,'Active'),
(1005,5,102,'2025-02-10',NULL,'Active');

📂 Step 10: Insert Sample Subscription Changes

INSERT INTO subscription_changes VALUES
(1,1001,'2025-03-01',101,102,'Upgrade'),
(2,1004,'2025-04-01',103,104,'Upgrade'),
(3,1005,'2025-05-01',102,101,'Downgrade');

📂 Step 11: Insert Sample Payments

INSERT INTO payments VALUES
(501,1001,'2025-02-01',999,'Paid'),
(502,1002,'2025-02-01',499,'Paid'),
(503,1003,'2025-02-01',4999,'Paid'),
(504,1004,'2025-02-01',2499,'Paid'),
(505,1005,'2025-02-01',999,'Paid');