🧠 SQL Concepts You'll Practice

✔ Joins

✔ GROUP BY

✔ HAVING

✔ CASE WHEN

✔ CTEs

✔ Subqueries

✔ Window Functions

✔ LAG()

✔ LEAD()

✔ Date Functions

✔ Conditional Aggregation

📊 Business KPIs You Can Build

📈 Monthly Recurring Revenue (MRR)

📈 Annual Recurring Revenue (ARR)

📈 Average Revenue Per User (ARPU)

📈 Customer Lifetime Value (CLV)

📈 Monthly Customer Churn Rate

📈 Revenue Churn Rate

📈 Customer Retention Rate

📈 New Customers, New MRR

📈 Expansion MRR, Contraction MRR, Churned MRR

📈 Net Revenue Retention (NRR), Gross Revenue Retention (GRR)

📈 Upgrade Rate, Downgrade Rate

📈 Plan-wise Revenue, Active Subscriptions, Cancelled Subscriptions

📈 Payment Success Rate, Failed Payment Rate

📈 Monthly Revenue Growth, Revenue Contribution by Plan

📈 Customer Segmentation

💡 Example 1: Calculate MRR

SELECT
    SUM(p.monthly_price) AS mrr
FROM subscriptions s
JOIN plans p ON s.plan_id = p.plan_id
WHERE s.subscription_status = 'Active';

💡 Example 2: Revenue by Plan

SELECT
    p.plan_name,
    COUNT(s.subscription_id) AS active_subscriptions,
    SUM(p.monthly_price) AS monthly_revenue
FROM subscriptions s
JOIN plans p ON s.plan_id = p.plan_id
WHERE s.subscription_status = 'Active'
GROUP BY p.plan_name
ORDER BY monthly_revenue DESC;

💡 Example 3: Identify Churned Customers

SELECT
    customer_id,
    subscription_id,
    end_date
FROM subscriptions
WHERE subscription_status = 'Cancelled';

💡 Example 4: Calculate Upgrade vs Downgrade Count

SELECT
    change_type,
    COUNT(*) AS total_changes
FROM subscription_changes
GROUP BY change_type;

💡 Example 5: Calculate Average Revenue Per Customer

SELECT
    ROUND(SUM(p.monthly_price) / COUNT(DISTINCT s.customer_id), 2) AS arpu
FROM subscriptions s
JOIN plans p ON s.plan_id = p.plan_id
WHERE s.subscription_status = 'Active';

💡 Example 6: Rank Customers by MRR

WITH customer_mrr AS (
    SELECT
        s.customer_id,
        SUM(p.monthly_price) AS mrr
    FROM subscriptions s
    JOIN plans p ON s.plan_id = p.plan_id
    WHERE s.subscription_status = 'Active'
    GROUP BY s.customer_id
)
SELECT
    customer_id,
    mrr,
    DENSE_RANK() OVER (ORDER BY mrr DESC) AS revenue_rank
FROM customer_mrr;

🎯 Key Insights You Can Derive

🔹 Which subscription plan generates the most revenue?

🔹 Which plan has the highest churn?

🔹 How much MRR comes from new customers?

🔹 How much revenue is lost through cancellations?

🔹 Which customers have upgraded or downgraded?

🔹 Is revenue growing month over month?

🔹 Which customers contribute the most recurring revenue?

💼 This project is especially useful for Data Analysts, Product Analysts, Revenue Analysts, Growth Analysts, and Business Intelligence professionals working with SaaS and subscription-based businesses.

Double Tap ❤️ For More