Saas.sql

create database saas

use saas

-- NULL VALUE AUDIT

-- Accounts
SELECT
    SUM(CASE WHEN referral_source IS NULL THEN 1 ELSE 0 END) AS referral_source_nulls,
    SUM(CASE WHEN seats IS NULL THEN 1 ELSE 0 END) AS seats_nulls
FROM accounts;

-- Churn Events
SELECT
    SUM(CASE WHEN feedback_text IS NULL THEN 1 ELSE 0 END) AS feedback_nulls
FROM churn_events;

-- Subscriptions
SELECT
    SUM(CASE WHEN end_date IS NULL THEN 1 ELSE 0 END) AS active_subscriptions,
    SUM(CASE WHEN mrr_amount IS NULL THEN 1 ELSE 0 END) AS mrr_nulls
FROM subscriptions;

-- Feature Usage
SELECT
    SUM(CASE WHEN error_count IS NULL THEN 1 ELSE 0 END) AS error_nulls
FROM feature_usage;

-- Support Tickets
SELECT
    SUM(CASE WHEN satisfaction_score IS NULL THEN 1 ELSE 0 END) AS satisfaction_nulls,
    SUM(CASE WHEN closed_at IS NULL THEN 1 ELSE 0 END) AS open_tickets
FROM support_tickets;

-- Churn Events table Null values 

SELECT
  ISNULL(feedback_text, 'No Feedback Provided') AS feedback_text,
  COUNT(*) AS churn_count
FROM churn_events
GROUP BY ISNULL(feedback_text, 'No Feedback Provided');

-- Support Tickets table

SELECT
  CASE
    WHEN satisfaction_score IS NULL THEN 'Not Rated'
    ELSE CAST(satisfaction_score AS VARCHAR)
  END AS satisfaction_bucket,
  COUNT(*) AS tickets
FROM support_tickets
GROUP BY
  CASE
    WHEN satisfaction_score IS NULL THEN 'Not Rated'
    ELSE CAST(satisfaction_score AS VARCHAR)
  END;

-- subscriptions

-- Active subscriptions
SELECT COUNT(*) 
FROM subscriptions
WHERE end_date IS NULL;

-- mrr_amount = 0

SELECT COUNT(*) 
FROM subscriptions
WHERE mrr_amount = 0;

-- Foreign Keys

ALTER TABLE subscriptions
ADD CONSTRAINT fk_sub_account
FOREIGN KEY (account_id) REFERENCES accounts(account_id);

ALTER TABLE churn_events
ADD CONSTRAINT fk_churn_account
FOREIGN KEY (account_id) REFERENCES accounts(account_id);

ALTER TABLE support_tickets
ADD CONSTRAINT fk_ticket_account
FOREIGN KEY (account_id) REFERENCES accounts(account_id);

ALTER TABLE feature_usage
ADD CONSTRAINT fk_usage_subscription
FOREIGN KEY (subscription_id) REFERENCES subscriptions(subscription_id);



-- Basic

-- 1. Total accounts by plan tier

SELECT plan_tier, COUNT(*) AS total_accounts
FROM dbo.accounts
GROUP BY plan_tier;

-- 2. Trial vs Paid accounts

SELECT is_trial, COUNT(*) AS total
FROM dbo.accounts
GROUP BY is_trial;

-- 3. Total active subscriptions

SELECT COUNT(*) AS active_subscriptions
FROM dbo.subscriptions
WHERE churn_flag = 0;

-- 4. Total number of accounts

SELECT COUNT(*) AS total_accounts
FROM dbo.accounts;

-- 5. Accounts by country

SELECT country, COUNT(*) AS accounts
FROM dbo.accounts
GROUP BY country
ORDER BY accounts DESC;

-- 6. Accounts by industry

SELECT industry, COUNT(*) AS total_accounts
FROM dbo.accounts
GROUP BY industry;

-- 7. Trial vs non-trial accounts

SELECT is_trial, COUNT(*) AS accounts
FROM dbo.accounts
GROUP BY is_trial;

--8. Total subscriptions

SELECT COUNT(*) AS total_subscriptions
FROM dbo.subscriptions;

-- 9. Active vs churned subscriptions

SELECT churn_flag, COUNT(*) AS subscriptions
FROM dbo.subscriptions
GROUP BY churn_flag;

-- 10. Average seats per plan

SELECT plan_tier, AVG(seats) AS avg_seats
FROM dbo.subscriptions
GROUP BY plan_tier;

-- 11. Monthly vs annual billing

SELECT billing_frequency, COUNT(*) AS subscriptions
FROM dbo.subscriptions
GROUP BY billing_frequency;

-- 12. Total support tickets

SELECT COUNT(*) AS total_tickets
FROM dbo.support_tickets;

-- 13. Tickets by priority

SELECT priority, COUNT(*) AS tickets
FROM dbo.support_tickets
GROUP BY priority;

-- 14. Features usage count

SELECT feature_name, COUNT(*) AS usage_events
FROM dbo.feature_usage
GROUP BY feature_name;

-- 15. Accounts that churned

SELECT COUNT(DISTINCT account_id) AS churned_accounts
FROM dbo.churn_events;

-- 16. Average MRR

SELECT AVG(mrr_amount) AS avg_mrr
FROM dbo.subscriptions
WHERE mrr_amount > 0;


-- 17. Signup trend (year-wise)

SELECT YEAR(signup_date) AS year, COUNT(*) AS accounts
FROM dbo.accounts
GROUP BY YEAR(signup_date)
ORDER BY year;

-- INTERMEDIATE

-- 1. Monthly Recurring Revenue (MRR)

SELECT
    SUM(mrr_amount) AS total_mrr
FROM dbo.subscriptions
WHERE churn_flag = 0;

-- 2. Churn rate by plan

SELECT
    a.plan_tier,
    COUNT(DISTINCT c.account_id) * 1.0
    / COUNT(DISTINCT a.account_id) AS churn_rate
FROM dbo.accounts a
LEFT JOIN dbo.churn_events c
ON a.account_id = c.account_id
GROUP BY a.plan_tier;

-- 3. Avg resolution time by priority

SELECT
    priority,
    AVG(resolution_time_hours) AS avg_resolution_hrs
FROM dbo.support_tickets
GROUP BY priority;

-- 4. Accounts with active subscriptions and their MRR - Which customers are currently active and how much revenue they generate?

SELECT
    a.account_id,
    a.account_name,
    SUM(s.mrr_amount) AS total_mrr
FROM dbo.accounts a
JOIN dbo.subscriptions s
    ON a.account_id = s.account_id
WHERE s.churn_flag = 0
GROUP BY a.account_id, a.account_name;

-- 5. Churned accounts with their churn reason- Why did customers leave?

SELECT
    a.account_name,
    c.reason_code,
    c.churn_date
FROM dbo.accounts a
JOIN dbo.churn_events c
    ON a.account_id = c.account_id;

--6. Average MRR by plan tier -Which plan generates more revenue per customer?

SELECT
    s.plan_tier,
    AVG(s.mrr_amount) AS avg_mrr
FROM dbo.subscriptions s
WHERE s.mrr_amount > 0
GROUP BY s.plan_tier;

-- 7. Support ticket count per account - Which customers contact support most?

SELECT
    a.account_id,
    a.account_name,
    COUNT(t.ticket_id) AS total_tickets
FROM dbo.accounts a
LEFT JOIN dbo.support_tickets t
    ON a.account_id = t.account_id
GROUP BY a.account_id, a.account_name;

--8. Feature usage frequency per subscription - How actively are customers using the product?

SELECT
    f.subscription_id,
    COUNT(f.usage_id) AS usage_events
FROM dbo.feature_usage f
GROUP BY f.subscription_id;

--9. Accounts with both support tickets and churn - Did support issues lead to churn?

SELECT DISTINCT
    a.account_id,
    a.account_name
FROM dbo.accounts a
JOIN dbo.support_tickets t
    ON a.account_id = t.account_id
JOIN dbo.churn_events c
    ON a.account_id = c.account_id;

-- 10. Revenue by billing frequency - Do monthly or annual plans earn more?

SELECT
    s.billing_frequency,
    SUM(s.arr_amount) AS total_arr
FROM dbo.subscriptions s
GROUP BY s.billing_frequency;


-- ADVANCED

-- 1. Feature usage drop before churn

WITH usage_stats AS (
    SELECT
        s.account_id,
        COUNT(f.usage_id) AS total_usage
    FROM subscriptions s
    LEFT JOIN feature_usage f
    ON s.subscription_id = f.subscription_id
    GROUP BY s.account_id
)
SELECT
    AVG(total_usage) AS avg_usage_before_churn
FROM usage_stats u
JOIN churn_events c
ON u.account_id = c.account_id;

-- 2. Support impact on churn

SELECT
    CASE
        WHEN t.resolution_time_hours > 48 THEN 'Slow Support'
        ELSE 'Good Support'
    END AS support_quality,
    COUNT(DISTINCT c.account_id) AS churned_accounts
FROM support_tickets t
JOIN churn_events c
ON t.account_id = c.account_id
GROUP BY
    CASE
        WHEN t.resolution_time_hours > 48 THEN 'Slow Support'
        ELSE 'Good Support'
    END;

-- 3. Revenue loss due to churn

SELECT
    SUM(s.arr_amount) AS churned_arr_loss
FROM subscriptions s
JOIN churn_events c
ON s.account_id = c.account_id;



-- EXPERT(WINDOW + PRODUCT METRICS)

-- 1. Account lifetime (days)

SELECT
    account_id,
    DATEDIFF(
        DAY,
        MIN(start_date),
        ISNULL(MAX(end_date), GETDATE())
    ) AS account_lifetime_days
FROM subscriptions
GROUP BY account_id;

-- 2. Upgrade → churn analysis

SELECT
    preceding_upgrade_flag,
    COUNT(*) AS churn_count
FROM churn_events
GROUP BY preceding_upgrade_flag;

-- 3. Reactivation Analysis - How many churned users came back?

SELECT
    is_reactivation,
    COUNT(*) AS accounts
FROM churn_events
GROUP BY is_reactivation;

-- 4. Churn Risk Segmentation (Pre-Churn Signals) - Which accounts are high-risk before churn?

SELECT
    a.plan_tier,
    AVG(t.resolution_time_hours) AS avg_support_time,
    AVG(s.mrr_amount) AS avg_mrr
FROM accounts a
JOIN subscriptions s ON a.account_id = s.account_id
LEFT JOIN support_tickets t ON a.account_id = t.account_id
GROUP BY a.plan_tier;

--5. Feature Adoption vs Revenue (Product Strategy)

SELECT
    f.feature_name,
    COUNT(DISTINCT s.account_id) AS users,
    AVG(s.mrr_amount) AS avg_mrr
FROM feature_usage f
JOIN subscriptions s ON f.subscription_id = s.subscription_id
GROUP BY f.feature_name;


-- WINDOW FUNCTIONS

-- 1. Rank accounts by MRR within each plan tier - Who are the top-paying customers in each plan?

SELECT
    s.account_id,
    s.plan_tier,
    s.mrr_amount,
    RANK() OVER (
        PARTITION BY s.plan_tier
        ORDER BY s.mrr_amount DESC
    ) AS mrr_rank
FROM dbo.subscriptions s
WHERE s.mrr_amount > 0;

-- 2. Running total of MRR by signup date - How is recurring revenue accumulating over time?

SELECT
    a.signup_date,
    SUM(s.mrr_amount) AS daily_mrr,
    SUM(SUM(s.mrr_amount)) OVER (
        ORDER BY a.signup_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_mrr
FROM dbo.accounts a
JOIN dbo.subscriptions s
    ON a.account_id = s.account_id
GROUP BY a.signup_date;

-- 3. Previous vs current MRR (MRR change detection) - Did revenue increase or decrease compared to the previous subscription?

SELECT
    subscription_id,
    account_id,
    mrr_amount,
    LAG(mrr_amount) OVER (
        PARTITION BY account_id
        ORDER BY start_date
    ) AS previous_mrr,
    mrr_amount
      - LAG(mrr_amount) OVER (
            PARTITION BY account_id
            ORDER BY start_date
        ) AS mrr_change
FROM dbo.subscriptions;

-- 4. Identify most-used feature per subscription - Which feature drives engagement for each customer?

SELECT *
FROM (
    SELECT
        subscription_id,
        feature_name,
        SUM(usage_count) AS total_usage,
        ROW_NUMBER() OVER (
            PARTITION BY subscription_id
            ORDER BY SUM(usage_count) DESC
        ) AS rn
    FROM dbo.feature_usage
    GROUP BY subscription_id, feature_name
) t
WHERE rn = 1;

-- 5. Support performance percentile ranking - Which tickets had unusually slow resolution times?

SELECT
    ticket_id,
    resolution_time_hours,
    PERCENT_RANK() OVER (
        ORDER BY resolution_time_hours
    ) AS resolution_percentile
FROM dbo.support_tickets;


-- Views for BI

--1. vw_accounts_clean - Purpose: Customer-level attributes - Used for slicers & filters in Power BI

CREATE VIEW vw_accounts_clean AS
SELECT
    account_id,
    account_name,
    industry,
    country,
    signup_date,
    plan_tier,
    seats,
    is_trial,
    churn_flag
FROM dbo.accounts;

-- 2. vw_active_subscriptions - Purpose: Revenue & billing logic - Used for MRR, ARR, plan analysis

CREATE VIEW vw_active_subscriptions AS
SELECT
    subscription_id,
    account_id,
    plan_tier,
    seats,
    mrr_amount,
    arr_amount,
    billing_frequency,
    start_date,
    end_date,
    churn_flag
FROM dbo.subscriptions;

--3. vw_churn_analysis - Purpose: Why customers churn - Used for churn reason charts

CREATE VIEW vw_churn_analysis AS
SELECT
    c.account_id,
    c.churn_date,
    c.reason_code,
    c.refund_amount_usd,
    c.preceding_upgrade_flag,
    c.preceding_downgrade_flag,
    c.is_reactivation
FROM dbo.churn_events c;

-- 4. vw_feature_engagement - Purpose: Product usage & engagement - Used for feature adoption visuals

CREATE VIEW vw_feature_engagement AS
SELECT
    f.subscription_id,
    f.feature_name,
    SUM(f.usage_count) AS total_usage,
    SUM(f.usage_duration_secs) AS total_duration,
    SUM(f.error_count) AS total_errors
FROM dbo.feature_usage f
GROUP BY
    f.subscription_id,
    f.feature_name;

-- 5. vw_support_kpis - Purpose: Support experience metrics - Used for support vs churn analysis

CREATE VIEW vw_support_kpis AS
SELECT
    account_id,
    COUNT(ticket_id) AS total_tickets,
    AVG(resolution_time_hours) AS avg_resolution_time,
    AVG(satisfaction_score) AS avg_satisfaction,
    SUM(CASE WHEN escalation_flag = 0 THEN 1 ELSE 0 END) AS escalations
FROM dbo.support_tickets
GROUP BY account_id;

-- 6. vw_saas_master - Purpose: ONE view for Power BI dashboard - This is the ONLY view Power BI needs

CREATE VIEW dbo.vw_saas_master
AS
SELECT
    a.account_id,
    a.account_name,
    a.industry,
    a.country,
    a.signup_date,

    s.subscription_id,   
    s.plan_tier,
    s.mrr_amount,
    s.arr_amount,
    s.billing_frequency,
    s.start_date,
    s.end_date,
    s.churn_flag,

    c.reason_code AS churn_reason,

    sp.total_tickets,
    sp.avg_resolution_time,
    sp.avg_satisfaction
FROM dbo.vw_accounts_clean a
LEFT JOIN dbo.vw_active_subscriptions s
    ON a.account_id = s.account_id
LEFT JOIN dbo.vw_churn_analysis c
    ON a.account_id = c.account_id
LEFT JOIN dbo.vw_support_kpis sp
    ON a.account_id = sp.account_id;
GO

-- vw_support_priority_kpis

CREATE VIEW dbo.vw_support_priority_kpis
AS
SELECT
    priority,
    COUNT(ticket_id) AS total_tickets,
    AVG(resolution_time_hours) AS avg_resolution_time,
    AVG(satisfaction_score) AS avg_satisfaction
FROM dbo.support_tickets
GROUP BY priority;
GO