15 Stripe Sigma SQL Queries Founders Actually Use

Quick answer

Active subscribers and MRR in one query. Paste it into the Sigma editor.

-- Stripe Sigma (Trino)
-- Paying customers and MRR per currency, right now
with per_customer as (
  select customer_id, currency, sum(mrr_change) as mrr
  from subscription_item_change_events_v2_beta
  group by customer_id, currency
)
select
  currency,
  count_if(mrr > 0) as paying_customers,
  sum(mrr) / 100.0 as mrr,
  round(sum(mrr) / 100.0 / nullif(count_if(mrr > 0), 0), 2) as arpa
from per_customer
group by currency

Stripe counts a customer as an active subscriber when their MRR is above zero. That leaves trials and fully discounted plans out.

Schema assumptions

Source	Tables used	Notes
Stripe Sigma	subscription_item_change_events_v2_beta, subscriptions, customers, products, balance_transactions (amount, fee, net, type, currency, created), charges, refunds (charge_id, amount, reason, created), invoices, disputes (charge_id, amount, reason, status, created)	Trino dialect. Amounts in minor units. Timestamps in UTC except local_event_timestamp.
Stripe Data Pipeline	Same tables under schema STRIPE	Adapt Trino-only functions to your warehouse.

All 15 queries below are Stripe Sigma (Trino). Example outputs use made-up data.

Copy-ready SQL

Subscriptions

1. Subscriptions by status

Purpose: the shape of your base in one glance. Change: nothing.

-- Stripe Sigma (Trino)
select status, count(*) as subscriptions
from subscriptions
group by status
order by subscriptions desc

status	subscriptions
active	412
canceled	977
trialing	38
past_due	9

2. MRR by month

Purpose: the MRR trend. Change: nothing. Full version with new, expansion and churn on the Stripe MRR SQL page.

-- Stripe Sigma (Trino)
with monthly as (
  select date_trunc('month', local_event_timestamp) as month, currency, sum(mrr_change) as mrr_change
  from subscription_item_change_events_v2_beta
  group by 1, 2
)
select month, currency, sum(mrr_change) over (partition by currency order by month) / 100.0 as ending_mrr
from monthly
order by month desc

month	currency	ending_mrr
2026-09-01	usd	18420.00

3. New paid subscriptions per month

Purpose: how fast you add paying customers. Counts subscription items that started contributing MRR. Change: nothing.

-- Stripe Sigma (Trino)
select
  date_trunc('month', local_event_timestamp) as month,
  count(distinct customer_id) as new_paying_customers,
  sum(mrr_change) / 100.0 as new_mrr
from subscription_item_change_events_v2_beta
where event_type = 'ACTIVE_START'
group by 1
order by 1 desc

month	new_paying_customers	new_mrr
2026-09-01	31	1490.00

4. Cancellations per month

Purpose: how many subscriptions you lose. Change: nothing.

-- Stripe Sigma (Trino)
select
  date_trunc('month', canceled_at) as month,
  count(*) as cancelled_subscriptions,
  count(distinct customer_id) as customers
from subscriptions
where canceled_at is not null
group by 1
order by 1 desc

month	cancelled_subscriptions	customers
2026-09-01	14	13

5. Trial conversion rate

Purpose: share of trials that ended in the last 90 days and turned into a paying subscription. Change: the window.

-- Stripe Sigma (Trino)
select
  count(*) as trials_ended,
  count_if(status in ('active', 'past_due')) as converted,
  round(100.0 * count_if(status in ('active', 'past_due')) / nullif(count(*), 0), 1) as conversion_pct
from subscriptions
where trial_end is not null
  and trial_end < current_timestamp
  and trial_end >= current_date - interval '90' day

trials_ended	converted	conversion_pct
120	41	34.2

6. Subscriptions scheduled to cancel

Purpose: customers who have cancelled but are still paid up. Your save list. Change: nothing.

-- Stripe Sigma (Trino)
select c.email, s.id, s.cancel_at as ends_on
from subscriptions s
join customers c on c.id = s.customer_id
where s.status = 'active' and s.cancel_at_period_end
order by s.cancel_at

email	id	ends_on
jo@startup.example	sub_1Q…	2026-10-31 00:00

7. MRR by product

Purpose: which plan carries the business. Change: nothing.

-- Stripe Sigma (Trino)
select p.name as product, e.currency, sum(e.mrr_change) / 100.0 as mrr
from subscription_item_change_events_v2_beta e
join products p on p.id = e.product_id
group by 1, 2
having sum(e.mrr_change) > 0
order by mrr desc

product	currency	mrr
Pro	usd	12600.00

8. Customers with more than one active subscription

Purpose: find duplicate or add-on subscriptions. Change: nothing.

-- Stripe Sigma (Trino)
select c.email, count(*) as active_subscriptions
from subscriptions s
join customers c on c.id = s.customer_id
where s.status in ('active', 'past_due')
group by c.email
having count(*) > 1
order by active_subscriptions desc

email	active_subscriptions
ops@acme.example	3

Revenue and payments

9. Gross volume, fees and net per month

Purpose: what you charged, what Stripe kept, what reached your balance. Change: nothing.

-- Stripe Sigma (Trino)
select
  date_trunc('month', created) as month,
  currency,
  sum(amount) / 100.0 as gross,
  sum(fee) / 100.0 as fees,
  sum(net) / 100.0 as net
from balance_transactions
where type = 'charge'
group by 1, 2
order by 1 desc

month	currency	gross	fees	net
2026-09-01	usd	21480.00	703.42	20776.58

10. Refunds per month

Purpose: refund volume, without partial-capture releases. Change: nothing.

-- Stripe Sigma (Trino)
select
  date_trunc('month', created) as month,
  currency,
  count(*) as refunds,
  sum(amount) / 100.0 as refunded
from refunds
where reason is null or reason != 'partial_capture'
group by 1, 2
order by 1 desc

month	currency	refunds	refunded
2026-09-01	usd	12	588.00

11. Failed charges by decline code

Purpose: why payments fail. Change: the window. More on the failed payments page.

-- Stripe Sigma (Trino)
select failure_code, count(*) as failed_charges, sum(amount) / 100.0 as failed_amount
from charges
where status = 'failed'
  and created >= current_date - interval '30' day
group by failure_code
order by failed_charges desc

failure_code	failed_charges	failed_amount
card_declined	61	2989.00

12. Past-due subscriptions with email

Purpose: who to chase today. Change: add 'unpaid' if your settings use it.

-- Stripe Sigma (Trino)
select c.email, s.id as subscription_id, s.status
from subscriptions s
join customers c on c.id = s.customer_id
where s.status in ('past_due')
order by c.email

email	subscription_id	status
billing@acme.example	sub_1P…	past_due

13. Disputes in the last 90 days

Purpose: chargebacks by reason and outcome. Change: the window.

-- Stripe Sigma (Trino)
select reason, status, count(*) as disputes, sum(amount) / 100.0 as disputed_amount
from disputes
where created >= current_date - interval '90' day
group by 1, 2
order by disputes desc

reason	status	disputes	disputed_amount
fraudulent	lost	3	147.00

Customers

14. Top customers by lifetime payments

Purpose: who has paid you the most, net of refunds. Change: the limit.

-- Stripe Sigma (Trino)
select
  c.email,
  ch.currency,
  sum(ch.amount - ch.amount_refunded) / 100.0 as lifetime_paid,
  count(*) as payments
from charges ch
join customers c on c.id = ch.customer_id
where ch.status = 'succeeded'
group by 1, 2
order by lifetime_paid desc
limit 20

email	currency	lifetime_paid	payments
ops@acme.example	usd	11880.00	12

15. New customers per month

Purpose: top of the funnel inside Stripe, including people who never paid. Change: nothing.

-- Stripe Sigma (Trino)
select date_trunc('month', created) as month, count(*) as new_customers
from customers
group by 1
order by 1 desc
limit 12

month	new_customers
2026-09-01	96