Query Billing data

select
  subscriptions.id,
  subscriptions.customer_id,
  customers.email
from subscriptions
inner join customers
  on customers.id = subscriptions.customer_id
where
  subscriptions.status = 'unpaid'
limit 5

select
  customers.id,
  customers.email,
  subscriptions.price_id
from subscriptions
inner join customers
on customers.id = subscriptions.customer_id
where subscriptions.status = 'trialing'
limit 5

with active_subscriptions as (
  select
    s.id as subscription_id,
    p.name as product_name,
    p.statement_descriptor
  from subscriptions s
  join subscription_items si on s.id = si.subscription_id
  join products p on si.price_product_id = p.id
  where s.status = 'active'
)
select
  subscription_id,
  subscription_item_id,
  price_id,
  product_name,
  statement_descriptor
from active_subscriptions
order by 1,2

with subscription_item_prices as (
  select
    si.subscription_id,
    si.price_id,
    p.currency
  from
    subscription_items si
  join prices p on si.price_id = p.id
),
price_tier_details as (
  select
    sp.subscription_id,
    pt.price_id,
    pt.upto,
    stringify_amount(sp.currency, pt.amount, '.') as tier_price,
    sp.currency
  from
    subscription_item_prices sp
  join price_tiers pt on sp.price_id = pt.price_id
)
select
  ptd.subscription_id,
  ptd.price_id,
  ptd.upto,
  ptd.tier_price,
  ptd.currency
from
  price_tier_details ptd
order by
  ptd.subscription_id,
  ptd.price_id,
  ptd.upto asc

select
  id,
  charge_id,
  amount_due
from invoices
where subscription_id = 'sub_ALJXL9gBYtv6GJ'

with invoices_with_discounts as (
  select
    invoice_id,
    sum(amount) as total_discount_amount
  from
    invoice_line_item_discount_amounts
  group by
    invoice_id
)
select
  i.id as invoice_id,
  i.period_start,
  i.period_end,
  stringify_amount(i.currency, ilda.total_discount_amount, '.') as total_discount_amount
  i.currency
from
  invoices i
  join invoices_with_discounts ilda on i.id = ilda.invoice_id
order by i.id

select
  coupons.id,
  coupons.amount_off,
  coupons.percent_off
from coupons
where valid = false
limit 5

select
  subscriptions.id as subscription_id,
  t.discount_id,
  coupons.id as coupon_id
from
  subscriptions
  cross join unnest(split(subscriptions.discounts, ',')) as t(discount_id)
  join discounts on discounts.id = t.discount_id
  join coupons on coupons.id = discounts.coupon_id
limit 3

select
  promotion_codes.id as promotion_code_id,
  promotion_codes.code as promotion_code,
  promotion_codes.times_redeemed
from
  promotion_codes
limit 3

WITH ts_grouped_sub_item_events AS (
  SELECT
    local_event_timestamp,
    customer_id,
    currency,
    sum(mrr_change) AS mrr_change
  FROM
    subscription_item_change_events
  GROUP BY
    1,
    2,
    3
),
ts_grouped_sub_item_events_with_mrr AS (
  SELECT
    *,
    date_trunc(
      'day',
      date(local_event_timestamp)
    ) AS local_event_date,
    -- Stripe defines an "active subscriber" as a customer with non-zero MRR.
    -- Therefore instead of summing up event_type to get subscription count (and its diff),
    -- We count the amount of revenue on each customer instead and later check its movement from / to zero
    sum(mrr_change) over (
      PARTITION by customer_id
      ORDER BY
        local_event_timestamp ASC
    ) AS mrr,
    -- We count the # of times MRR has actually changed, and use nullif to ignore events that do not impact MRR
    -- Otherwise we may confuse between new vs. reactivation
    count(nullif(mrr_change, 0)) over (
      PARTITION by customer_id
      ORDER BY
        local_event_timestamp ASC
    ) AS mrr_change_count
  FROM
    ts_grouped_sub_item_events
),
ts_grouped_sub_item_events_with_previous_mrr AS (
  SELECT
    *,
    coalesce(
      last_value(mrr) IGNORE nulls OVER (
        PARTITION by customer_id
        ORDER BY
          local_event_timestamp ASC ROWS BETWEEN UNBOUNDED PRECEDING
          AND 1 PRECEDING
      ),
      0
    ) AS previous_mrr
  FROM
    ts_grouped_sub_item_events_with_mrr
),
customer_events AS (
  SELECT
    *,
    CASE
      WHEN mrr = 0
      AND previous_mrr > 0 THEN 'ACTIVE_END'
      WHEN mrr > 0
      AND previous_mrr = 0
      AND mrr_change_count = 1 THEN 'ACTIVE_START'
      WHEN mrr > 0
      AND previous_mrr = 0
      AND mrr_change_count > 1 THEN 'REACTIVATE'
      WHEN mrr > previous_mrr THEN 'ACTIVE_UPGRADE'
      WHEN mrr < previous_mrr THEN 'ACTIVE_DOWNGRADE'
      ELSE NULL
    END AS cus_event_type
  FROM
    ts_grouped_sub_item_events_with_previous_mrr
),
date_grouped_customer_events AS (
  SELECT
    local_event_date,
    currency,
    sum(mrr_change) AS mrr_change,
    sum(
      CASE
        cus_event_type
        WHEN 'ACTIVE_START' THEN mrr_change
        ELSE 0
      END
    ) AS new_mrr,
    sum(
      CASE
        cus_event_type
        WHEN 'REACTIVATE' THEN mrr_change
        ELSE 0
      END
    ) AS reactivation_mrr,
    sum(
      CASE
        cus_event_type
        WHEN 'ACTIVE_UPGRADE' THEN mrr_change
        ELSE 0
      END
    ) AS expansion_mrr,
    sum(
      CASE
        cus_event_type
        WHEN 'ACTIVE_DOWNGRADE' THEN mrr_change
        ELSE 0
      END
    ) AS contraction_mrr,
    sum(
      CASE
        cus_event_type
        WHEN 'ACTIVE_END' THEN mrr_change
        ELSE 0
      END
    ) AS churn_mrr,
    sum(
      CASE
        WHEN mrr = 0
        AND previous_mrr > 0 THEN -1
        WHEN mrr > 0
        AND previous_mrr = 0 THEN 1
        ELSE 0
      END
    ) AS active_subscribers_change,
    sum(
      CASE
        cus_event_type
        WHEN 'ACTIVE_END' THEN 1
        ELSE 0
      END
    ) AS churned_subscribers,
    sum(
      CASE
        cus_event_type
        WHEN 'ACTIVE_START' THEN 1
        ELSE 0
      END
    ) AS new_subscribers,
    sum(
      CASE
        cus_event_type
        WHEN 'REACTIVATE' THEN 1
        ELSE 0
      END
    ) AS reactivated_subscribers
  FROM
    customer_events
  GROUP BY
    1,
    2
),
-- Prepare the multi dimensional table with all days + currency combinations and conversion rate metadata
-- note that exchange_rates_from_usd contains one row for every date from 2010-01-07 until today
-- which is why we don't need to generate a separate date series for the full table
dates_with_rate_per_usd AS (
  SELECT
    -- We use previous day's closing rates in precomputed metrics
    date - INTERVAL '1' DAY AS fx_date,
    cast(
      json_parse(buy_currency_exchange_rates) AS map(varchar, double)
    ) AS rate_per_usd
  FROM
    exchange_rates_from_usd
),
currencies AS (
  SELECT
    DISTINCT(currency)
  FROM
    subscription_item_change_events
),
first_default_currency AS (
  SELECT
    default_currency
  FROM
    accounts
  WHERE
    default_currency IS NOT NULL
  LIMIT
    1
),
dates_x_currencies_with_conversion_rate AS (
  SELECT
    fx_date as local_date,
    currency,
    default_currency,
    1 / rate_per_usd [currency] * rate_per_usd [coalesce(default_currency, 'usd')] AS conversion_rate
  FROM
    dates_with_rate_per_usd
    CROSS JOIN currencies
    CROSS JOIN first_default_currency
  ORDER BY
    1,
    2
),
daily_metrics_by_currency AS (
  SELECT
    dpc.local_date,
    dpc.currency,
    dpc.conversion_rate,
    coalesce(
      sum(mrr_change) over (
        PARTITION by dpc.currency
        ORDER BY
          dpc.local_date ASC
      ),
      0
    ) AS mrr,
    coalesce(
      round(
        sum(mrr_change) over (
          PARTITION by dpc.currency
          ORDER BY
            dpc.local_date ASC
        ) * dpc.conversion_rate
      ),
      0
    ) AS converted_mrr,
    coalesce(round(new_mrr * conversion_rate), 0) AS converted_new_mrr,
    coalesce(round(reactivation_mrr * conversion_rate), 0) AS converted_reactivation_mrr,
    coalesce(round(expansion_mrr * conversion_rate), 0) AS converted_expansion_mrr,
    coalesce(round(contraction_mrr * conversion_rate), 0) AS converted_contraction_mrr,
    coalesce(round(churn_mrr * conversion_rate), 0) AS converted_churn_mrr,
    coalesce(dgce.mrr_change, 0) AS mrr_change,
    coalesce(dgce.new_mrr, 0) AS new_mrr,
    coalesce(dgce.reactivation_mrr, 0) AS reactivation_mrr,
    coalesce(dgce.expansion_mrr, 0) AS expansion_mrr,
    coalesce(dgce.contraction_mrr, 0) AS contraction_mrr,
    coalesce(dgce.churn_mrr, 0) AS churn_mrr,
    coalesce(
      sum(active_subscribers_change) over (
        PARTITION by dpc.currency
        ORDER BY
          dpc.local_date ASC
      ),
      0
    ) AS active_subscribers,
    coalesce(dgce.active_subscribers_change, 0) AS active_subscribers_change,
    coalesce(dgce.churned_subscribers, 0) AS churned_subscribers,
    coalesce(dgce.new_subscribers, 0) AS new_subscribers,
    coalesce(dgce.reactivated_subscribers, 0) AS reactivated_subscribers
  FROM
    dates_x_currencies_with_conversion_rate dpc
    LEFT JOIN date_grouped_customer_events dgce ON dpc.local_date = dgce.local_event_date
    AND dpc.currency = dgce.currency
),
daily_metrics AS (
  SELECT
    local_date,
    sum(converted_mrr) AS mrr,
    sum(converted_new_mrr) AS new_mrr,
    sum(converted_reactivation_mrr) AS reactivation_mrr,
    sum(converted_expansion_mrr) AS expansion_mrr,
    sum(converted_contraction_mrr) AS contraction_mrr,
    sum(converted_churn_mrr) AS churn_mrr,
    -- Customer can only have active subscription in a single currency at a time, as a result this does not result in over-counting subscriber changes
    -- This also matches the precomputed metrics logic in billing dashboard / CSV download
    sum(active_subscribers) AS active_subscribers,
    sum(churned_subscribers) AS churned_subscribers,
    sum(new_subscribers) AS new_subscribers,
    sum(reactivated_subscribers) AS reactivated_subscribers
  FROM
    daily_metrics_by_currency
  GROUP BY
    1
),
daily_metrics_with_derived AS (
  SELECT
    *,
    mrr - lag(mrr) over (
      ORDER BY
        local_date
    ) - new_mrr - reactivation_mrr - expansion_mrr - contraction_mrr - churn_mrr AS fx_adjustment_mrr,
    lag(mrr) over (
      ORDER BY
        local_date
    ) AS previous_mrr
  FROM
    daily_metrics
),
-- Turn daily into monthly metrics
monthly_metrics_with_derived AS (
  SELECT
    date_trunc('month', local_date) AS local_month_start,
    max_by(mrr, local_date) AS ending_mrr,
    sum(new_mrr) AS new_mrr,
    sum(reactivation_mrr) AS reactivation_mrr,
    sum(expansion_mrr) AS expansion_mrr,
    sum(contraction_mrr) AS contraction_mrr,
    sum(churn_mrr) AS churn_mrr,
    sum(fx_adjustment_mrr) AS fx_adjustment_mrr,
    max_by(active_subscribers, local_date) AS ending_subscribers,
    sum(churned_subscribers) AS churned_subscribers,
    sum(new_subscribers) AS new_subscribers,
    sum(reactivated_subscribers) AS reactivated_subscribers
  FROM
    daily_metrics_with_derived
  GROUP BY
    1
)
SELECT
  local_month_start,
  ending_mrr - fx_adjustment_mrr - churn_mrr - contraction_mrr - expansion_mrr - reactivation_mrr - new_mrr AS beginning_mrr,
  new_mrr,
  reactivation_mrr,
  expansion_mrr,
  contraction_mrr,
  churn_mrr,
  fx_adjustment_mrr,
  ending_mrr,
  -- Churned subscribers is a positive number in CSV reports instead of negative for churn / contraction mrr
  ending_subscribers - (-1 * churned_subscribers) - reactivated_subscribers - new_subscribers AS beginning_subscribers,
  new_subscribers,
  reactivated_subscribers,
  churned_subscribers,
  ending_subscribers
FROM
  monthly_metrics_with_derived
ORDER BY
  1 DESC