SaaS Subscriptions SQL Practice Dataset

select industry, count(*) as organizations
from organizations
group by industry
order by organizations desc;

select count(*) as live_subscriptions
from subscriptions
where ended_on is null;

select status,
       count(*) as invoices,
       round(sum(amount), 2) as total
from invoices
group by status
order by total desc;

select p.tier,
       count(*) as subscriptions,
       round(avg(s.seats), 1) as avg_seats
from subscriptions s
join plans p on p.plan_id = s.plan_id
group by p.tier
order by avg_seats desc;

select event_type, count(*) as system_events
from usage_events
where user_id is null
group by event_type
order by system_events desc;

with months as (
  select distinct date_trunc('month', period_start) as month
  from invoices
),
month_ends as (
  select month, month + interval 1 month - interval 1 day as month_end
  from months
),
live as (
  select m.month,
         p.monthly_price + p.seat_price * s.seats as mrr
  from month_ends m
  join subscriptions s
    on s.started_on <= m.month_end
   and (s.ended_on is null or s.ended_on > m.month_end)
  join plans p on p.plan_id = s.plan_id
)
select month,
       count(*) as live_subscriptions,
       round(sum(mrr), 2) as mrr
from live
group by month
order by month;

select date_trunc('month', issued_on) as month,
       round(sum(amount), 2) as invoiced,
       round(sum(amount) filter (where status = 'paid'), 2) as collected,
       round(sum(amount) filter (where status <> 'paid'), 2) as uncollected
from invoices
group by month
order by month;

select cancel_reason,
       count(*) as cancellations,
       round(100.0 * count(*) / sum(count(*)) over (), 1) as pct_of_cancellations
from subscriptions
where cancel_reason is not null
group by cancel_reason
order by cancellations desc;

select o.org_name,
       count(*) as subscriptions,
       bool_or(s.ended_on is null) as currently_subscribed
from subscriptions s
join organizations o on o.organization_id = s.organization_id
group by o.org_name
having count(*) > 1
order by subscriptions desc, o.org_name;

select o.org_name,
       count(*) as events,
       count(distinct e.user_id) as active_users
from usage_events e
join organizations o on o.organization_id = e.organization_id
group by o.org_name
order by events desc
limit 20;

with first_sub as (
  select organization_id,
         date_trunc('month', min(started_on)) as cohort_month
  from subscriptions
  group by organization_id
),
still_on as (
  select f.organization_id,
         f.cohort_month,
         max(case
               when s.started_on <= (f.cohort_month + interval 6 month)
                and (s.ended_on is null or s.ended_on > (f.cohort_month + interval 6 month))
               then 1 else 0
             end) as retained
  from first_sub f
  join subscriptions s on s.organization_id = f.organization_id
  group by f.organization_id, f.cohort_month
)
select cohort_month,
       count(*) as organizations,
       sum(retained) as retained_after_6_months,
       round(100.0 * sum(retained) / count(*), 1) as retention_pct
from still_on
group by cohort_month
order by cohort_month;

with ordered as (
  select organization_id,
         started_on,
         ended_on,
         lag(ended_on) over (partition by organization_id order by started_on) as previous_ended_on
  from subscriptions
)
select o.org_name,
       ordered.previous_ended_on,
       ordered.started_on,
       date_diff('day', ordered.previous_ended_on, ordered.started_on) as gap_days
from ordered
join organizations o on o.organization_id = ordered.organization_id
where ordered.previous_ended_on is not null
  and ordered.started_on > ordered.previous_ended_on
order by gap_days desc
limit 25;

with valued as (
  select s.organization_id,
         s.started_on,
         p.monthly_price + p.seat_price * s.seats as mrr,
         row_number() over (partition by s.organization_id order by s.started_on) as seq,
         count(*) over (partition by s.organization_id) as total
  from subscriptions s
  join plans p on p.plan_id = s.plan_id
)
select o.org_name,
       round(max(case when seq = 1 then mrr end), 2) as first_mrr,
       round(max(case when seq = total then mrr end), 2) as latest_mrr,
       round(max(case when seq = total then mrr end) - max(case when seq = 1 then mrr end), 2) as change
from valued v
join organizations o on o.organization_id = v.organization_id
where total > 1
group by o.org_name
order by change desc;

with flagged as (
  select s.subscription_id,
         s.ended_on,
         max(case when i.status = 'failed' then 1 else 0 end) as had_failure
  from subscriptions s
  left join invoices i on i.subscription_id = s.subscription_id
  group by s.subscription_id, s.ended_on
)
select case when had_failure = 1 then 'had a failed invoice' else 'never failed' end as cohort,
       count(*) as subscriptions,
       count(*) filter (where ended_on is not null) as cancelled,
       round(100.0 * count(*) filter (where ended_on is not null) / count(*), 1) as cancel_pct
from flagged
group by had_failure
order by cancel_pct desc;

with cutoff as (
  select max(occurred_at) - interval 90 day as since from usage_events
),
recent as (
  select distinct organization_id
  from usage_events, cutoff
  where occurred_at >= cutoff.since
)
select o.org_name,
       p.plan_name,
       s.seats
from subscriptions s
join organizations o on o.organization_id = s.organization_id
join plans p on p.plan_id = s.plan_id
where s.ended_on is null
  and not exists (select 1 from recent r where r.organization_id = s.organization_id)
order by s.seats desc;