# Query billing data

Use Sigma or Data Pipeline to retrieve billing information.

Billing is made up of different components that work together to provide one-off *invoices* (Invoices are statements of amounts owed by a customer. They track the status of payments from draft through paid or otherwise finalized. Subscriptions automatically generate invoices, or you can manually create a one-off invoice) and periodic billing, with different aspects of billing data available across a number of tables. All billing-specific tables are in the **Billing** section of the schema, with the primary tables being `subscriptions` and `invoices`.

To explore billing data further, you can use the additional tables that represent the components of subscriptions and invoices, such as `prices`, `products`, or `coupons`. In addition, the `customers` table is a fundamental part of billing, and contains data you might need to report on.

## Subscriptions

Each row within the `subscriptions` table represents data about an individual [Subscription](https://docs.stripe.com/api.md#subscription_object) object—the same information that  the API retrieves or is available in the [Stripe Dashboard](https://dashboard.stripe.com/test/subscriptions). You can report on every subscription that you create on your account.

This table is our recommended starting point for creating reports about your current subscribers. You can join this to other related tables, allowing you to explore your data in more detail.
![](https://b.stripecdn.com/docs-statics-srv/assets/subscriptions.b2676e216021e4a013eb4da5f4f839ec.png)

The following example retrieves a list of subscriptions that have been marked as unpaid, along with any available contact information for the customer.

```sql
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
```

| id | customer_id | email |
| --- | --- | --- |
| sub_orWziM4j7CiRL8J | cus_orWziM4j7CiRL8J | jenny.rosen@example.com |
| sub_orWziM4j7CiRL8J | cus_orWziM4j7CiRL8J | noah.wilson@example.com |
| sub_orWziM4j7CiRL8J | cus_orWziM4j7CiRL8J | joshua.miller@example.com |
| sub_orWziM4j7CiRL8J | cus_orWziM4j7CiRL8J | madison.jackson@example.com |
| sub_orWziM4j7CiRL8J | cus_orWziM4j7CiRL8J | elijah.smith@example.com |

## Customers

Data about [Customer](https://docs.stripe.com/api.md#customers) objects are contained in the `customers` table (this isn’t part of the Billing Tables group). It’s commonly used as part of billing-based reports and can be joined to a number of tables. It’s also useful if you’re creating [charges](https://docs.stripe.com/data/query-transactions.md) with saved payment information.
![](https://b.stripecdn.com/docs-statics-srv/assets/customers.60b1588c3375261e7f8b6ec1a4ab8302.svg)

The following example retrieves a list of customers with subscriptions that are currently in a trial period. It retrieves both the ID and email address for each customer.

```sql
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
```

| id | email | price_id |
| --- | --- | --- |
| cus_orWziM4j7CiRL8J | jenny.rosen@example.com | ruby-pro-522 |
| cus_orWziM4j7CiRL8J | noah.wilson@example.com | ruby-pro-522 |
| cus_orWziM4j7CiRL8J | richard.jones@example.com | gold-basic-221 |
| cus_orWziM4j7CiRL8J | madison.jackson@example.com | gold-basic-221 |
| cus_orWziM4j7CiRL8J | elijah.smith@example.com | silver-pro-498 |

## Products and prices

Products describe items that your customers can purchase with a subscription. Prices are tied to products and set out the cost, billing interval, and currency. When you view data from the `subscriptions` table, you can join it with `subscription_items`. Additionally, you can join it to `products.id` by using the `price_product_id` from the item.

The following example returns a list of active subscriptions along with the product name and its statement descriptor:

```sql
with active_subscriptions as (
  select
    s.id as subscription_id,
    si.id as subscription_item_id,
    si.price_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
```

| id | name | statement_descriptor |
| --- | --- | --- |
| sub_orWziM4j7CiRL8J | ruby-pro-522 | Ruby Pro |
| sub_orWziM4j7CiRL8J | gold-basic-221 | Gold Basic |
| sub_orWziM4j7CiRL8J | silver-pro-498 | Silver Pro |
| sub_orWziM4j7CiRL8J | diamond-mid-244 | Diamond Mid |
| sub_orWziM4j7CiRL8J | ruby-standard-196 | Ruby Standard |

## Price tiers

While using prices with [tiers](https://docs.stripe.com/products-prices/pricing-models.md#tiered-pricing) in your subscriptions, the `price_tiers` table can provide specific data about each tier. For instance, if you want to understand the initial tier of your subscriptions, including the maximum quantity for the first tier and the used unit amount, refer to the following query:

```sql
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
```

| subscription_id | price_id | upto | tier_price | currency |
| --- | --- | --- | --- | --- |
| sub_orWziM4j7CiRL8J | price_orWziM4j7CiRL8J | 30 | 2.00 | usd |
| sub_orWziM4j7CiRL8J | price_orWziM4j7CiRL8J | 60 | 1.00 | usd |
| sub_orWziM4j7CiRL8J | price_orWziM4j7CiRL8J | 90 | 0.50 | usd |

## Invoices

Refer to our [invoices](https://docs.stripe.com/invoicing/overview.md) documentation to learn more about invoices, invoice items, and invoice line items.

The `invoices` table contains data about individual [Invoice](https://docs.stripe.com/api.md#invoice_object) objects. Each subscription generates an invoice on a recurring basis that represents the amount the customer owes. This automatically includes the amount required for the subscription, and any additional [invoice items](https://docs.stripe.com/api.md#invoiceitems) that might have been created (listed as line items).

Invoices are comprised of individual ([invoice) line items](https://docs.stripe.com/api.md#invoice_line_item_object). These line items represent any subscriptions that the customer is billed for, and invoice items that have been created and applied to the invoice. To break down an invoice and analyze each of its line items, use the `invoice_line_items` table.

The  `source_id` column of this table contains the **ID** of either the subscription (for example, `sub_orWziM4j7CiRL8J`) or invoice item (for example, `ii_orWziM4j7CiRL8J`) that the line item corresponds to. The `source_type` column reflects whether the line items represent a subscription or an invoice item.

Unlike other foreign keys, the `subscription` column of the  `invoice_line_items` table isn’t always populated. If the corresponding invoice item is a subscription, this column is blank—its **ID** already appears in the `source_id` column.

### Invoice items

Data about [Invoice items](https://docs.stripe.com/api.md#invoiceitems) is provided in the `invoice_items` table. Invoice items are commonly used to specify an additional amount (or deduct an amount) that’s applied on the next invoice at the beginning of the next billing cycle. For example, you would create an invoice item if you need to bill your customer for exceeding their monthly allowance, or if you need to provide a credit on the next invoice for unused service.
![](https://b.stripecdn.com/docs-statics-srv/assets/invoices.ed8822925c41f368821ba104bd52efb1.svg)

The following example retrieves all the invoices and associated charge IDs for a particular subscription.

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

| id | name |
| --- | --- |
| in_orWziM4j7CiRL8J | ch_orWziM4j7CiRL8J | 1999 |
| in_orWziM4j7CiRL8J | ch_orWziM4j7CiRL8J | 1999 |
| in_orWziM4j7CiRL8J | 1999 | ch_orWziM4j7CiRL8J |
| in_orWziM4j7CiRL8J | 1999 | ch_orWziM4j7CiRL8J |
| in_orWziM4j7CiRL8J | 1999 | ch_orWziM4j7CiRL8J |

### Invoice totals and discounts

The invoice subtotal represents the amount of all subscriptions, invoice items, and prorations on the invoice before any discount is applied. The invoice total is the amount after discounts and tax have been applied:

`invoice.total` = `invoice.subtotal` - `discount` + `invoice.tax`

There is no column to represent the discount amount on an invoice. Instead, you can calculate this by aggregating the line items’ discount amounts. The following query returns a list of invoices, their period start and end, the total discounted amount for the invoice.

```sql
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
```

| invoice_id | period_start | period_end | total_discount_amount | currency |
| --- | --- | --- | --- | --- |
| in_orWziM4j7CiRL8J | 2024-05-01 | 2024-06-01 | 24.66 | usd |
| in_orWziM4j7CiRL8J | 2024-06-01 | 2024-07-01 | 24.34 | usd |
| in_orWziM4j7CiRL8J | 2024-04-01 | 2024-05-01 | 45.96 | usd |

### Working with invoice dates and periods

Subscription invoices are pre-billed, meaning the customer makes the payment at the beginning of a billing cycle. This is represented in a line item’s `period` value. For example, a customer with a monthly subscription is billed at the start of each month. If they choose to [cancel_at_period_end](https://docs.stripe.com/api/subscriptions/object.md#subscription_object-cancel_at_period_end), their subscription stays active until the month’s end, after which the subscription ends.

The `period_start` and `period_end` values of an invoice represents when invoice items might have been created–it’s not always definitive of the period of service that the customer is being billed for. For example, if a customer is billed on the 1st of each month and exceeds their monthly allowance on the 15th, you might create an invoice item for any additional costs that the customer is charged for. This invoice item is then included in the next invoice, which is created on the 1st of the next month. When the next invoice is generated, the `period_start` date would be the 15th of the previous month—the date the additional line item is first created.

## Usage based billing

[Usage-based billing](https://docs.stripe.com/billing/subscriptions/usage-based.md) enables you to charge customers based on their usage of your product or service.

### Billing meters

A [Meter](https://docs.stripe.com/api/billing/meter.md) object specifies how to aggregate meter events over a billing period. Meter events represent all actions that customers take in your system (for example, API requests). Meters attach to prices and form the basis of what’s billed. These objects are available through the `billing_meters` table.

The following query returns all active billing meters.

```sql
select
  id,
  status,
  display_name,
  default_aggregation_formula
from
  billing_meters
where
  status = 'ACTIVE'
  and livemode
```

| ID | status | display_name | default_aggregation_formula |
| --- | --- | --- | --- |
| mtr_orWziM4j7CiRL8J | ACTIVE | alpaca_ai_token | SUM |
| mtr_orWziM4j7CiRL8J | ACTIVE | alpaca_ai_image_token | COUNT |

### Billing meter event summaries

A [Billing Meter Event Summary](https://docs.stripe.com/api/billing/meter-event-summary.md) object represents an aggregated view of a customer’s billing meter events within a specified timeframe. It represents how much usage a customer accrues for that period. These objects are available through the `billing_meter_event_summaries` table. Hourly summaries are available, as indicated by the `value_grouping_window` column.

The following query returns a sum of billing meter events for a specific customer.

```sql
select
  billing_meters.display_name,
  sum(billing_meter_event_summaries.aggregated_value) AS total_usage
from
  billing_meter_event_summaries
  join billing_meters on billing_meters.id = billing_meter_event_summaries.meter_id
where
  billing_meter_event_summaries.customer_id = 'cus_EDQkYj7P2Jf3sJ1'
  and billing_meter_event_summaries.start_time >= timestamp '2025-02-01 08:00'
  and billing_meter_event_summaries.end_time <= timestamp '2025-02-01 20:00'
  and value_grouping_window = 'hourly'
group by
  display_name
```

| display_name | total_usage |
| --- | --- |
| alpaca_ai_token | 227416 |
| alpaca_ai_image_token | 22 |

### Billing meter usage analytics

[Billing Meter Usage Analytics](https://docs.stripe.com/api/billing/analytics/meter-usage.md) objects represent an analytics summary of a customer’s billing meter usage within a specified timeframe. It can be grouped by or filtered by meters, dimensions, and tenants to power customer analytics dashboards.

The [integration guide](https://docs.stripe.com/billing/subscriptions/usage-based/analytics.md) demonstrates request and response shapes.

This API is available in public preview. [Request access to this API](https://docs.stripe.com/data/query-billing-data.md#billing_meter_usage_api_preview).

### Gain access to the Billing Meter Usage Summary API

Enter your email to request access.

```bash
curl https://docs.stripe.com/preview/register \
  -X POST \
  -H "Content-Type: application/json" \
  -H "Referer: https://docs.stripe.com/data/query-billing-data" \
  -d '{"email": "EMAIL", "preview": "billing_meter_usage_api_preview"}'
```

### Billing meter invalid events

A [Billing Meter Invalid Event](https://docs.stripe.com/billing/subscriptions/usage-based/recording-usage-api.md#handle-meter-event-errors) object represents a billing meter event that isn’t successfully validated. These objects are available through the `billing_meter_invalid_events` table. The associated `billing_meter_invalid_events_payload` table contains the event payload from the original event.

The following query returns all invalid billing meter events for a specific customer.

```sql
SELECT
  billing_meter_invalid_events.id as event_id,
  billing_meter_invalid_events.error_code,
  billing_meter_invalid_events.error_message
FROM
  billing_meter_invalid_events
  JOIN billing_meter_invalid_events_payload ON billing_meter_invalid_events_payload.event_id = billing_meter_invalid_events.id
WHERE
  billing_meter_invalid_events_payload.key = 'stripe_customer_id'
  AND billing_meter_invalid_events_payload.value = 'cus_EDQkYj7P2Jf3sJ1'
```

| event_id | error_code | error_message |
| --- | --- | --- |
| orWziM4j_orWz_orWz_orWz_orWziM4j7CiR | METER_NOT_FOUND | No meter found matching event_name mtr_orWziM4j7CiRL8J. |
| orWziM4j_orWz_orWz_orWz_orWziM4j7CiR | METER_NOT_FOUND | No meter found matching event_name mtr_orWziM4j7CiRL8J. |

## Coupons

A [Coupon](https://docs.stripe.com/billing/subscriptions/coupons.md) object represents an amount or percentage-off discount that you can apply to subscriptions or customers.

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

| id | amount_off | percent_off |
| --- | --- | --- |
| 10FF |  | 10 |
| SUMMER25 |  | 25 |
| 10FREE | 10 |  |
| 15OFF |  | 15 |
| FALL30 | 30 |  |

## Discounts

A discount is the application of a coupon, represented by a [Discount](https://docs.stripe.com/api.md#discounts) object. The following query returns a list of subscriptions and their associated discounts and coupons:

```sql
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
```

| subscription_id | discount_id | coupon_id |
| --- | --- | --- |
| sub_orWziM4j7CiRL8J | di_orWziM4j7CiRL8J | 10OFF |
| sub_orWziM4j7CiRL8J | di_orWziM4j7CiRL8J | 25OFF |
| sub_orWziM4j7CiRL8J | di_orWziM4j7CiRL8J | 10FREE |

## Promotion codes

A [promotion code](https://docs.stripe.com/api/promotion_codes.md) represents a customer-redeemable code for a [coupon](https://docs.stripe.com/billing/subscriptions/coupons.md). The following query provides a list of promotion codes pertaining to a specific coupon and displays the number of times each code has been redeemed:

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

| promotion_code_id | code | times_redeemed |
| --- | --- | --- |
| promo_orWziM4j7CiRL8J | 10OFF | 1 |
| promo_orWziM4j7CiRL8J | 25OFF | 2 |
| promo_orWziM4j7CiRL8J | 10FREE | 3 |

## Subscription Item Change Events

The `subscription_item_change_events` table tracks changes to subscription items that affect [Monthly Recurring Revenue (MRR)](https://support.stripe.com/questions/calculating-monthly-recurring-revenue-\(mrr\)-in-billing) and subscription quantities. Use this table to calculate MRR for individual customers, products, or plans, to create custom metric definitions for your business models, and to track subscription quantity changes.

> This table provides more up-to-date data than the source driving the MRR metrics on the Billing overview in the Stripe Dashboard. This means the data for the last and current day’s MRR here could be more accurate and could differ from what you see in the Dashboard.

### Subscription Item Change Events v2 (Public preview)

The `subscription_item_change_events_v2_beta` table supersedes the `subscription_item_change_events` [table](https://docs.stripe.com/data/query-billing-data.md#subscription-item-change-events) with improved data freshness. Data in this table maintains a 3 hour freshness in Sigma and a 7 hour freshness in Stripe Data Pipeline. It shares the same schema with the existing dataset. You can query it by appending the `v2_beta` suffix to the table in the same example template queries.

> #### Data might change
> 
> This table provides data that is fresher and more consistent with the Stripe Dashboard than the existing dataset. This means that future deliveries of this dataset might have updated data for the last and current day’s MRR. Allow all data to settle (48-hours maximum) before consuming this data incrementally.

### local_event_timestamp and event_timestamp

This table includes two timestamp columns:

- `event_timestamp`: This is the UTC timestamp.
- `local_event_timestamp`: This timestamp is in your local timezone, typically the timezone of the person who created your Stripe account.

### currency

Here, you’ll find the subscription item’s settlement currency as a three-letter [ISO currency code](https://docs.stripe.com/currencies.md) in lowercase. The currency must be one that Stripe [supports](https://docs.stripe.com/currencies.md).

### mrr_change

The `mrr_change` column shows the positive or negative impact of an event on your MRR in the subscription item’s settlement currency’s minor unit (such as cents for USD).

### quantity_change

The `quantity_change` column shows the associated positive or negative change in the quantity of a subscription item that a customer subscribes to.

### event_type

| Event type | Definition |
| --- | --- |
| ACTIVE_START | The subscription item started contributing to MRR. |
| ACTIVE_END | The subscription item stopped contributing to MRR. |
| ACTIVE_UPGRADE | The MRR contribution of the subscription item increased. This can occur when the price of a subscription item increases or if the quantity of that subscription item increases. |
| ACTIVE_DOWNGRADE | The MRR contribution of the subscription item decreased. This can occur when the price of a subscription item decreases or if the quantity of that subscription item decreases. |
| ACTIVE_QUANTITY_INCREASE | The quantity of the subscription item increased, but the MRR wasn’t impacted. You might see this if you use tiered pricing and the quantity needs to exceed a certain threshold before the price changes. |
| ACTIVE_QUANTITY_DECREASE | The quantity of the subscription item decreased, but the MRR wasn’t impacted. You might see this if you use tiered pricing and the quantity needs to go below a certain threshold before the price changes. |

> Some user actions can create multiple events, so you could see an event with an `event_type` of `ACTIVE_END` on one item and then immediately an event with an `event_type` of `ACTIVE_START` on another item for the same `subscription_id`.

### Other columns

Other columns (`product_id`, `price_id`, `customer_id`, `subscription_id`, and `subscription_item_id`) hold IDs related to the subscription item change event.

### Example queries

For additional and most up-to-date examples, see the [Subscriptions section of query template library in Sigma sidebar](https://dashboard.stripe.com/sigma/queries).

To calculate the monthly recurring revenue (MRR) and the number of active subscribers from this table, you’ll need to use window functions. Additionally, if you have customers using different currencies, you’ll need to perform foreign currency exchange calculations. The calculation aims to track monthly MRR and the evolution of active subscribers, distinguishing between new additions, reactivations, expansions, contractions, and churns. The final results are presented in minor currency units, such as cents for USD.

```sql
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_v2_beta
  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
-- 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_v2_beta
),
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 doesn't 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
```

| local_month_start | beginning_mrr | new_mrr | reactivation_mrr | expansion_mrr | contraction_mrr | churn_mrr | fx_adjustment_mrr | ending_mrr | beginning_subscribers | new_subscribers | reactivated_subscribers | churned_subscribers | ending_subscribers |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| 2024-05-01 | 100072149 | 104000 | 0 | 40000 | 0 | 0 | 0 | 100216149 | 9 | 3 | 0 | 0 | 12 |
| 2024-04-01 | 100065149 | 7180 | 0 | 0 | 0 | -180 | 0 | 100072149 | 7 | 3 | 0 | 0 | 12 |
| 2024-03-01 | 100066099 | 124 | 0 | 0 | 0 | -1074 | 0 | 100065149 | 7 | 2 | 0 | 2 | 7 |
| 2024-02-01 | 100066099 | 1000 | 0 | 0 | 0 | -1000 | 0 | 100066099 | 7 | 1 | 0 | 1 | 7 |
| 2024-01-01 | 100038102 | 29216 | 0 | 1998 | -175 | -3042 | 0 | 100066099 | 5 | 4 | 0 | 2 | 7 |
| 2023-12-01 | 100038102 | 0 | 0 | 0 | 0 | 0 | 0 | 100038102 | 5 | 0 | 0 | 0 | 5 |
| 2023-11-01 | 100037102 | 1000 | 0 | 0 | 0 | 0 | 0 | 100038102 | 4 | 1 | 0 | 0 | 5 |
| 2023-10-01 | 100037102 | 0 | 0 | 0 | 0 | 0 | 0 | 100037102 | 4 | 0 | 0 | 0 | 4 |
| 2023-09-01 | 100037102 | 0 | 0 | 0 | 0 | 0 | 0 | 100037102 | 4 | 0 | 0 | 0 | 4 |
| 2023-08-01 | 100033902 | 0 | 0 | 5000 | 0 | -1800 | 0 | 100037102 | 5 | 0 | 0 | 1 | 4 |
| 2023-07-01 | 100037065 | 0 | 0 | 0 | 0 | -3159 | -4 | 100033902 | 6 | 0 | 0 | 1 | 5 |
| 2023-06-01 | 100036402 | 35 | 3369 | 0 | 0 | -2742 | 1 | 100037065 | 6 | 1 | 3 | 4 | 6 |
| 2023-05-01 | 100034898 | 2748 | 0 | 30437 | -83 | -31598 | 0 | 100036402 | 7 | 3 | 0 | 4 | 6 |
| 2023-04-01 | 100034065 | 933 | 0 | 0 | 0 | -100 | 0 | 100034898 | 6 | 2 | 0 | 1 | 7 |
| 2023-03-01 | 100002715 | 31350 | 0 | 0 | 0 | 0 | 0 | 100034065 | 4 | 2 | 0 | 0 | 6 |
| 2023-02-01 | 100006048 | 6086 | 0 | 6088 | 0 | -15507 | 0 | 100002715 | 5 | 2 | 0 | 3 | 4 |
| 2023-01-01 | 100006048 | 3043 | 0 | 0 | 0 | -3043 | 0 | 100006048 | 5 | 1 | 0 | 1 | 5 |
| 2022-12-01 | 100152134 | 25910 | 0 | 1363600 | -30000 | -1505574 | -22 | 100006048 | 9 | 6 | 0 | 10 | 5 |
| 2022-11-01 | 100178232 | 48688 | 3333 | 621878 | -10600 | -689397 | 0 | 100152134 | 7 | 16 | 1 | 15 | 9 |
| 2022-10-01 | 100036193 | 136333 | 120000 | 20600 | -10000 | -124894 | 0 | 100178232 | 7 | 4 | 2 | 6 | 7 |