Mizan Cloud SaaS

SELECT date_trunc('month', created_at)::date AS signup_month,
       acquisition_channel,
       count(*) AS accounts
FROM accounts
WHERE NOT is_test_account
GROUP BY 1, 2
ORDER BY 1, 2;

SELECT p.plan_name, count(*) AS subscriptions, sum(s.mrr) AS mrr
FROM subscriptions s
JOIN plans p ON p.plan_id = s.plan_id
JOIN accounts a ON a.account_id = s.account_id
WHERE NOT a.is_test_account
  AND NOT s.is_trial
  AND s.started_at <= DATE '2025-12-31'
  AND (s.ended_at IS NULL OR s.ended_at > DATE '2025-12-31')
GROUP BY p.plan_name
ORDER BY mrr DESC;

SELECT a.acquisition_channel,
       count(*) AS trials,
       count(*) FILTER (WHERE t.status = 'superseded') AS converted,
       round(100.0 * count(*) FILTER (WHERE t.status = 'superseded') / count(*), 1) AS conversion_pct
FROM subscriptions t
JOIN accounts a ON a.account_id = t.account_id
WHERE t.is_trial AND t.status <> 'trialing' AND NOT a.is_test_account
GROUP BY 1
ORDER BY conversion_pct DESC;

SELECT cancel_reason, count(*) AS churned_subscriptions
FROM subscriptions
WHERE status = 'cancelled' AND NOT is_trial
GROUP BY cancel_reason
ORDER BY 2 DESC;

SELECT date_trunc('week', e.occurred_at)::date AS week, count(DISTINCT e.user_id) AS active_users
FROM events e
JOIN accounts a ON a.account_id = e.account_id
WHERE e.event_name = 'login' AND NOT a.is_test_account
  AND e.occurred_at >= TIMESTAMP '2025-10-01'
GROUP BY 1
ORDER BY 1;