# 請求データのクエリ

Sigma または Data Pipeline を使用して、請求情報を取得します。

Billing は、相互に連携する複数のコンポーネントで構成され、複数のテーブルのさまざまな側面の請求データを使用して、1 回限りの*請求書* (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)や定期請求に対応します。主要なテーブルである `subscriptions`、`invoices` をはじめ、Billing 固有のテーブルはすべて、スキーマの **Billing** セクションにあります。

請求データをさらに調べるには、`prices`、`products`、`coupons` など、サブスクリプションや請求書のコンポーネントを示すその他のテーブルも利用できます。また、`customers` テーブルは Billing の基本的部分であり、レポート作成に必要なデータが収録されています。

## サブスクリプション

`subscriptions` テーブルの各行は、個別の [Subscription (サブスクリプション)](https://docs.stripe.com/api.md#subscription_object) オブジェクトに関するデータを示します。API でも、[Stripe ダッシュボード](https://dashboard.stripe.com/test/subscriptions)内でも、同じ情報を取得できます。アカウントで作成するすべてのサブスクリプションに関するレポートを作成できます。

現在のサブスクリプション登録者についてのレポートを作成する際には、このテーブルから開始することをお勧めします。このテーブルは、他の関連テーブルに結合できるため、データをさらに詳細に調べることができます。
![](https://b.stripecdn.com/docs-statics-srv/assets/subscriptions.b2676e216021e4a013eb4da5f4f839ec.png)

以下の例では、未払いとしてマークされたサブスクリプションと、その顧客の利用可能な連絡先情報のリストを取得します。

```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         | メール                         |
| ------------------- | ------------------- | --------------------------- |
| sub_orWziM4j7CiRL8J | cus_orWziM4j7CiRL8J | kaoru.hanamura@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    |

## 顧客

[Customer (顧客)](https://docs.stripe.com/api.md#customers) オブジェクトに関するデータは、`customers` テーブルに収録されています (これは、Billing テーブルグループには含まれません)。一般に請求ベースのレポートの一部として使用され、多数のテーブルと結合できます。保存された決済情報を使用して[支払い](https://docs.stripe.com/stripe-data/query-transactions.md)を作成する場合にも利用できます。
![](https://b.stripecdn.com/docs-statics-srv/assets/customers.60b1588c3375261e7f8b6ec1a4ab8302.svg)

次の例では、現在トライアル期間中のサブスクリプションの顧客のリストを取得します。各顧客の ID とメールアドレスの両方が取得されます。

```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                  | メール                         | price_id       |
| ------------------- | --------------------------- | -------------- |
| cus_orWziM4j7CiRL8J | kaoru.hanamura@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 |

## 商品と価格

商品は、顧客がサブスクリプションで購入できるアイテムを記述します。価格は商品に関連付けられており、コスト、請求期間、通貨を設定します。 `subscriptions` テーブルのデータを表示するときは、`subscription_items` と結合できます。さらに、アイテムの `price_product_id` を使用して、`products.id` に結合できます。

以下の例では、有効なサブスクリプションのリストを、商品名とその明細書表記とともに返します。

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

| ID                  | 名前                | 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        |

## 段階制料金

サブスクリプションで[段階](https://docs.stripe.com/products-prices/pricing-models.md#tiered-pricing)が設定された価格を使用している場合は、`price_tiers` テーブルで各段階に固有のデータを取得できます。たとえば、最初の段階の最大数量と使用されるユニット金額を含む、サブスクリプションの初期段階を把握するには、次のクエリを参照してください。

```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              | 最大 | tier_price | 通貨  |
| ------------------- | --------------------- | -- | ---------- | --- |
| sub_orWziM4j7CiRL8J | price_orWziM4j7CiRL8J | 30 | 2.00       | usd |
| sub_orWziM4j7CiRL8J | price_orWziM4j7CiRL8J | 60 | 1.00       | usd |
| sub_orWziM4j7CiRL8J | price_orWziM4j7CiRL8J | 90 | 0.50       | usd |

## 請求書

請求書、請求書アイテム、請求書のラインアイテムについて、詳細は Stripe の[請求書](https://docs.stripe.com/invoicing/overview.md)のドキュメントをご覧ください。

`invoices` テーブルには、個別の [Invoice (請求書)](https://docs.stripe.com/api.md#invoice_object) オブジェクトに関するデータが収録されます。サブスクリプションのそれぞれから、顧客が支払うべき金額を示す請求書が反復的に生成されます。これにはサブスクリプションに必要な金額のほかに、追加で [Invoice Item (請求書アイテム)](https://docs.stripe.com/api.md#invoiceitems) が (ラインアイテムとして) 作成されている場合はそれらも自動的に含められます。

請求書は、個々の [(Invoice) Line Items ((請求書) ラインアイテム)](https://docs.stripe.com/api.md#invoice_line_item_object) から構成されます。これらのラインアイテムは、顧客に対して請求されるサブスクリプションと、その請求書 に対して作成および適用される請求書アイテムを表します。請求書の内訳を調べてラインアイテムのそれぞれを分析するには、`invoice_line_items` テーブルを使用します。

このテーブルの `source_id` 列には、そのラインアイテムに対応するサブスクリプション (例: `sub_orWziM4j7CiRL8J`) または請求書アイテム (例: `ii_orWziM4j7CiRL8J`) の **ID** が記載されます。`source_type` 列では、そのラインアイテムがサブスクリプションと請求書アイテムのいずれを表しているかが示されます。

他の外部キーとは異なり、`invoice_line_items` テーブルの `subscription` 列は、必ず入力されるとは限りません。対応する請求書アイテムがサブスクリプションである場合は、この列がブランクになります (その **ID** はすでに `source_id` 列に表示されています)。

### 請求書アイテム

[請求書アイテム](https://docs.stripe.com/api.md#invoiceitems)に関するデータは、`invoice_items` テーブルに示されます。一般に請求書アイテムは、次の請求サイクルの開始時に次回の請求書に適用される追加金額 (または差し引き金額) の指定に使用されます。請求書アイテムは、たとえば月間使用量を超えた顧客に請求する必要がある場合や、次回の請求書で未使用分のサービスのクレジットを提供する必要がある場合に作成します。
![](https://b.stripecdn.com/docs-statics-srv/assets/invoices.ed8822925c41f368821ba104bd52efb1.svg)

次の例では、特定のサブスクリプションのすべての請求書と関連する支払い ID を取得します。

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

| ID                 | 名前                 |
| ------------------ | ------------------ |
| 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.total` = `invoice.subtotal` - `discount` + `invoice.tax`

割引額を請求書に表示するための列はありません。代わりに、ラインアイテムの割引額を集計することでこれを計算できます。以下のクエリは、請求書、その期間の開始日と終了日、請求書の合計割引額のリストを返します。

```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 | 通貨  |
| ------------------ | ------------ | ---------- | --------------------- | --- |
| 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 |

### 請求書の日付と期間の処理

サブスクリプションの請求書は事前に請求されるため、顧客は請求サイクルの開始時に支払いを行います。これは、ラインアイテムの `period` 値で表されます。たとえば、月次のサブスクリプションを利用している顧客は、毎月始めに請求されます。 [cancel_at_period_end](https://docs.stripe.com/api/subscriptions/object.md#subscription_object-cancel_at_period_end) を選択すると、サブスクリプションは月末まで有効であり、その後サブスクリプションは終了します。

請求書の `period_start` と `period_end` の値は、請求書アイテムがいつ作成されたかを表しますが、必ずしも顧客に請求されるサービス期間を完全に表しているわけではありません。たとえば、毎月 1 日に請求を受ける顧客が、15 日にその月間使用量を超えた場合、 顧客に請求される追加費用の請求書アイテムを作成することができます。この請求書アイテムは翌月 1 日に作成される次回の請求書に含まれます。次回の請求書を生成するときは、`period_start` は前月の 15 日 (追加のラインアイテムが最初に作成された日付) になります。

## 従量課金ベースの請求

[従量課金](https://docs.stripe.com/billing/subscriptions/usage-based.md)を使用すると、商品やサービスの使用量に基づいて顧客に請求できます。

### 請求メーター

[Meter](https://docs.stripe.com/api/billing/meter.md) オブジェクトは、請求期間中のメーターイベントを集計する方法を指定します。メーターイベントは、顧客がシステムで実行するすべてのアクション (API リクエストなど) を表します。メーターは価格に関連付けられ、請求内容の基礎を形成します。これらのオブジェクトは、`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 Summary](https://docs.stripe.com/api/billing/meter-event-summary.md) オブジェクトは、指定した期間内の顧客の請求メーターイベントの集計ビューを表します。これは、その期間に顧客が発生した使用量を表します。これらのオブジェクトは、`billing_meter_event_summaries` テーブルから使用できます。`value_grouping_window` 列に示されているように、時間ごとのサマリーを使用できます。

次のクエリは、特定の顧客の請求メーターイベントの合計を返します。

```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 使用状況分析](https://docs.stripe.com/api/billing/analytics/meter-usage.md) オブジェクトは、指定された期間における顧客の Billing メーター使用状況の分析サマリーを表します。メーター、ディメンション、テナントでグループ化したり、フィルタリングしたりして、顧客分析ダッシュボードを強化できます。

[導入ガイド](https://docs.stripe.com/billing/subscriptions/usage-based/analytics.md) では、リクエストとレスポンスの形状を説明しています。

この API は公開プレビューで利用できます。この API へのアクセスをリクエストするには、

### 請求メーターの無効なイベント

[Billing Meter Invalid Event](https://docs.stripe.com/billing/subscriptions/usage-based/recording-usage-api.md#handle-meter-event-errors) オブジェクトは、正常に検証されなかった請求メーターイベントを表します。これらのオブジェクトは、`billing_meter_invalid_events` テーブルから使用できます。関連付けられた `billing_meter_invalid_events_payload` テーブルには、元のイベントのイベントペイロードが含まれています。

次のクエリは、特定の顧客の無効な請求メーターイベントをすべて返します。

```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 | event_name mtr_orWziM4j7CiRL8J に一致するメーターが見つかりませんでした。 |
| orWziM4j*orWz*orWz*orWz*orWziM4j7CiR | METER_NOT_FOUND | event_name mtr_orWziM4j7CiRL8J に一致するメーターが見つかりませんでした。 |

## クーポン

[Coupon (クーポン)](https://docs.stripe.com/billing/subscriptions/coupons.md) オブジェクトは、サブスクリプションや顧客に適用できる、金額または率による割引を表します。

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

## 割引

割引はクーポンの適用であり、[Discount](https://docs.stripe.com/api.md#discounts) オブジェクトで表されます。以下のクエリは、サブスクリプションとそれに関連付けられた割引とクーポンのリストを返します。

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

## プロモーションコード

[プロモーションコード](https://docs.stripe.com/api/promotion_codes.md)  は、顧客が引き換え可能な[クーポン](https://docs.stripe.com/billing/subscriptions/coupons.md)のコードを表します。以下のクエリは、特定のクーポンに関連するプロモーションコードのリストを提供し、各コードが引き換えられた回数を表示します。

```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     | コード    | times_redeemed |
| --------------------- | ------ | -------------- |
| promo_orWziM4j7CiRL8J | 10OFF  | 1              |
| promo_orWziM4j7CiRL8J | 25OFF  | 2              |
| promo_orWziM4j7CiRL8J | 10FREE | 3              |

## サブスクリプションアイテム変更イベント

`subscription_item_change_events` テーブルは、[月間経常収益 (MRR)](https://support.stripe.com/questions/calculating-monthly-recurring-revenue-\(mrr\)-in-billing) に影響するサブスクリプションアイテムおよびサブスクリプションの数量の変更を追跡します。この表を使用して、個人顧客、商品、プランの MRR を計算し、ビジネスモデルのカスタム指標定義を作成し、サブスクリプションの数量の変更を追跡します。

> この表は、Stripe ダッシュボードの Billing の概要に表示される MRR 基準を構成するソースよりも最新のデータを提供します。このため、ここの前日の MRR のデータはより正確である可能性があり、ダッシュボードでの表示とは異なる可能性があります 。

### サブスクリプション項目変更イベント v2 (パブリックプレビュー)

`subscription_item_change_events_v2_beta` テーブルは、`subscription_item_change_events` [table](https://docs.stripe.com/stripe-data/query-billing-data.md#subscription-item-change-events) よりも優先され、データ鮮度が向上しています。このテーブルのデータは、Sigma で 3 時間の鮮度、Stripe Data Pipeline で 7 時間の鮮度を維持します。既存のデータセットと同じスキーマを共有します。同じサンプルテンプレートクエリのテーブルに `v2_beta` サフィックスを追加することで、クエリを実行できます。

> #### データが変更される可能性があります
> 
> この表は、既存のデータセットよりも新鮮で、Stripe ダッシュボードとの整合性が高いデータを提供します。このため、このデータセットの今後の配信では、前日と当日の MRR のデータが更新された可能性があります。このデータを増分的に取り込む前に、すべてのデータを確定させる (最大 48 時間) ようにします。

### local_event_timestamp and event_timestamp

このテーブルには、次の 2 つのタイムスタンプ列が含まれます。

- `event_timestamp`: これは UTC タイムスタンプです。
- `local_event_timestamp`: このタイムスタンプは、お客様のタイムゾーン (通常は、お客様の Stripe アカウントを作成した人物のタイムゾーン) です。

### 通貨

ここでは、サブスクリプションアイテムの売上処理通貨が、小文字の 3 文字の [ISO 通貨コード](https://docs.stripe.com/currencies.md)として記載されています。通貨は、Stripe が[サポートしている](https://docs.stripe.com/currencies.md)ものでなければなりません。

### mrr_change

`mrr_change` 列は、サブスクリプション項目の決済通貨の補助単位 (USD の場合はセントなど) の MRR に対するイベントのプラスの影響またはマイナスの影響を示します。

### quantity_change

`quantity_change` 列は、顧客が登録しているサブスクリプションアイテムの数量に関わる、プラスまたはマイナスの変化を表します。

### event_type

| イベントタイプ                  | 定義                                                                                                  |
| ------------------------ | --------------------------------------------------------------------------------------------------- |
| ACTIVE_START             | サブスクリプションアイテムが MRR への入金を開始した。                                                                       |
| ACTIVE_END               | サブスクリプションアイテムが MRR への入金を停止した。                                                                       |
| ACTIVE_UPGRADE           | サブスクリプションアイテムの MRR は増加しました。これは、サブスクリプションアイテムの価格が上がった場合、またはそのサブスクリプションアイテムの数量が増えた場合に発生する場合があります。     |
| ACTIVE_DOWNGRADE         | サブスクリプションアイテムの MRR は減少しました。これは、サブスクリプションアイテムの価格が下がった場合、またはそのサブスクリプションアイテムの数量が減った場合に発生する場合があります。     |
| ACTIVE_QUANTITY_INCREASE | サブスクリプションアイテムの数量は増えましたが、MRR には影響しませんでした。段階制料金を使用していて、価格変更の前に数量が特定のしきい値を上回る必要がある場合に、これが表示されることがあります。 |
| ACTIVE_QUANTITY_DECREASE | サブスクリプションアイテムの数量は減りましたが、MRR には影響しませんでした。段階制料金を使用していて、価格変更の前に数量が特定のしきい値を下回る必要がある場合に、これが表示されることがあります。 |

> 一部のユーザーアクションでは複数のイベントが作成されることがあります。このため、あるアイテムで `event_type` が `ACTIVE_END` のイベントが表示され、その後すぐに、同じ `subscription_id` の別のアイテムで `event_type` が `ACTIVE_START` のイベントが表示されることがあります。

### その他の列

その他の列 (`product_id`、`price_id`、`customer_id`、`subscription_id`、`subscription_item_id`) には、サブスクリプションアイテム変更イベントに関連する ID が格納されます。

### クエリ例

その他の最新の例については、[Sigma サイドバーのクエリテンプレートライブラリーのサブスクリプションセクション](https://dashboard.stripe.com/sigma/queries)をご覧ください。

このテーブルから月間経常収益 (MRR) と有効な登録者の数を計算するには、ウィンドウ関数を使用する必要があります。さらに、異なる通貨を使用する顧客が存在する場合は、外貨為替を計算する必要があります。この計算の目的は、月次の MRR と有効なサブスクリプション登録者の変化を追跡し、新規追加、再有効化、拡大、縮小、解約を区別することです。最終結果は、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
-- 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_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 年 2 月 1 日    | 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                  |