Stripe Failed Payments and Past-Due Subscriptions in SQL

Quick answer

Failed charges in the last 30 days, grouped by decline code. Paste into the Sigma editor.

-- Stripe Sigma (Trino)
-- Failed charges in the last 30 days by decline code
select
  failure_code,
  currency,
  count(*) as failed_charges,
  count(distinct customer_id) as customers,
  sum(amount) / 100.0 as failed_amount
from charges
where status = 'failed'
  and created >= current_date - interval '30' day
group by failure_code, currency
order by failed_charges desc

Schema assumptions

Source	Tables used	Notes
Stripe Sigma	charges (id, customer_id, invoice_id, amount, currency, status, failure_code, failure_message, card_brand, created), invoices (id, customer_id, subscription_id, status, amount_due, amount_paid, amount_remaining, attempt_count, next_payment_attempt, created, currency), subscriptions (id, customer_id, status), customers (id, email)	Amounts in minor units. Invoice status is draft, open, paid, uncollectible or void.
Stripe Data Pipeline	Same tables under schema STRIPE
Fivetran	charge, invoice, customer, subscription_history (same *_id columns)	Singular table names. Use _fivetran_active rows of subscription_history.
Airbyte	charges, invoices, subscriptions (customer, invoice, subscription, epoch created)	API-shaped column names.

Copy-ready SQL

1. Past-due subscriptions with the amount owed

Your dunning list: who is behind, by how much, and when Stripe tries again. Change: add 'unpaid' to the status list if your settings leave subscriptions unpaid after retries.

-- Stripe Sigma (Trino)
select
  c.email,
  s.id as subscription_id,
  s.status,
  i.currency,
  sum(i.amount_remaining) / 100.0 as amount_owed,
  max(i.attempt_count) as attempts,
  min(i.next_payment_attempt) as next_retry
from subscriptions s
join invoices i on i.subscription_id = s.id and i.status = 'open'
join customers c on c.id = s.customer_id
where s.status in ('past_due')
group by 1, 2, 3, 4
order by amount_owed desc

Example output (made-up data):

email	subscription_id	status	currency	amount_owed	attempts	next_retry
billing@acme.example	sub_1P…	past_due	usd	490.00	2	2026-10-14 09:00
jo@startup.example	sub_1Q…	past_due	usd	49.00	1	2026-10-13 18:30

2. Invoices Stripe is still retrying

Open invoices with at least one failed attempt and a retry scheduled. Change: nothing.

-- Stripe Sigma (Trino)
select
  i.id as invoice_id,
  c.email,
  i.amount_remaining / 100.0 as amount_remaining,
  i.currency,
  i.attempt_count,
  i.next_payment_attempt
from invoices i
join customers c on c.id = i.customer_id
where i.status = 'open'
  and i.attempt_count > 0
  and i.next_payment_attempt is not null
order by i.next_payment_attempt

3. Failed payment rate by month

Share of subscription payment attempts that fail. Change: drop the invoice_id filter to include one-off payments.

-- Stripe Sigma (Trino)
select
  date_trunc('month', created) as month,
  count(*) as attempts,
  count_if(status = 'failed') as failed,
  round(100.0 * count_if(status = 'failed') / count(*), 2) as failed_pct
from charges
where invoice_id is not null
  and created >= date_trunc('month', current_date) - interval '12' month
group by 1
order by 1 desc

Example output (made-up data):

month	attempts	failed	failed_pct
2026-09-01	1840	97	5.27
2026-08-01	1765	88	4.99

4. Revenue recovered by retries

Invoices that failed at least once and were paid later. Change: the 90-day window.

-- Stripe Sigma (Trino)
select
  date_trunc('month', created) as month,
  currency,
  count(*) as recovered_invoices,
  sum(amount_paid) / 100.0 as recovered_amount
from invoices
where status = 'paid'
  and attempt_count > 1
  and created >= current_date - interval '90' day
group by 1, 2
order by 1 desc

5. Failures by card brand and decline code

Spot a brand or issuer that declines more than the rest. Change: the 90-day window.

-- Stripe Sigma (Trino)
select
  card_brand,
  failure_code,
  count(*) as failed_charges
from charges
where status = 'failed'
  and created >= current_date - interval '90' day
group by 1, 2
order by failed_charges desc
limit 20

Example output (made-up data):

card_brand	failure_code	failed_charges
Visa	card_declined	142
MasterCard	insufficient_funds	51
Visa	expired_card	33

6. Past-due list from Fivetran tables (Postgres)

The same dunning list on Fivetran’s schema. Change: the stripe schema name.

-- PostgreSQL (Fivetran Stripe schema "stripe")
select
  c.email,
  s.id as subscription_id,
  i.currency,
  sum(i.amount_remaining) / 100.0 as amount_owed,
  max(i.attempt_count) as attempts,
  min(i.next_payment_attempt) as next_retry
from stripe.subscription_history s
join stripe.invoice i on i.subscription_id = s.id and i.status = 'open'
join stripe.customer c on c.id = s.customer_id
where s._fivetran_active
  and s.status = 'past_due'
group by 1, 2, 3
order by amount_owed desc;

7. Failed charges this week from Airbyte tables (Postgres)

Airbyte keeps API names and Unix timestamps. Change: the airbyte_stripe schema name.

-- PostgreSQL (Airbyte Stripe schema "airbyte_stripe")
select
  to_timestamp(created)::date as day,
  failure_code,
  count(*) as failed_charges,
  sum(amount) / 100.0 as failed_amount
from airbyte_stripe.charges
where status = 'failed'
  and livemode
  and to_timestamp(created) >= now() - interval '7 days'
group by 1, 2
order by 1 desc, 3 desc;