Customer LTV = ARPC / Churn rate

| invoice_id | subscriber_id | month         | amount_dollars |
| ---------- | ------------- | ------------- | -------------- |
| N001       | S001          | January 2021  | 100            |
| N002       | S002          | January 2021  | 150            |
| N003       | S001          | February 2021 | 100            |
| N004       | S002          | February 2021 | 150            |
| N005       | S003          | February 2021 | 200            |
| N006       | S001          | March 2021    | 100            |
| N007       | S003          | March 2021    | 200            |
| ...        | ...           | ...           | ...            |

| subscriber_id | active | monthly_invoice | created_at    | cancelled_at |
| ------------- | ------ | --------------- | ------------- | ------------ |
| S001          | Yes    | 100             | January 2021  |              |
| S002          | No     | 150             | January 2021  | March 2021   |
| S003          | Yes    | 200             | February 2021 |              |
| ...           | ...    | ...             | ...           | ...          |

| month         | invoice_id | subscriber_id | dollar_change | change_type |
| ------------- | ---------- | ------------- | ------------- | ----------- |
| January 2021  | N001       | S001          | 100           | new         |
| January 2021  | N002       | S002          | 150           | new         |
| February 2021 | N003       | S001          | 0             | retain      |
| February 2021 | N004       | S002          | 0             | retain      |
| February 2021 | N005       | S003          | 200           | new         |
| March 2021    | N006       | S001          | 0             | retain      |
| March 2021    | N007       | S002          | -150          | removed     |
| March 2021    | N008       | S003          | 0             | retain      |
| ...           | ...        | ...           | ...           | ...         |

SELECT
    month,
    sum(amount_dollars) AS mrr
FROM
    invoices
GROUP BY month

| month         | MRR |
| ------------- | --- |
| January 2021  | 250 |
| February 2021 | 450 |
| March 2021    | 300 |

sum(amount_dollars) AS mrr,

SELECT
    month,
    sum(amount_dollars) AS mrr,
    count(DISTINCT subscription_id) AS subscriptions,
    (mrr / subscriptions) AS arpc
FROM
    invoices
GROUP BY
    month

| month         | MRR | subscriptions | ARPC |
| ------------- | --- | ------------- | ---- |
| January 2021  | 250 | 2             | 125  |
| February 2021 | 450 | 3             | 150  |
| March 2021    | 300 | 2             | 150  |

(mrr / subscriptions) AS arpc

WITH total_subscriptions AS (
    SELECT
        date_trunc('month', invoices.date) AS month,
        count(DISTINCT invoices.subscription_id) AS subscriptions,
        sum(amount_dollars) AS mrr
    FROM
        invoices
    GROUP BY
        1
),
churned_subscriptions AS (
    SELECT
        s.month,
        s.subscriptions,
        s.mrr,
        lag(subscriptions) OVER (ORDER BY s.month) AS last_month_subscriptions,
        count(DISTINCT CASE WHEN revenue_changes.change_type = 'removed' THEN
                revenue_changes.subscription_id
            END) AS churned_subscriptions
    FROM
        total_subscriptions s
        LEFT JOIN revenue_changes ON s.month = revenue_changes.month
    GROUP BY
        1,
        2,
        3
)

| month         | churned_subscriptions | last_month_subscriptions |
| ------------- | --------------------- | ------------------------ |
| January 2021  |                       |                          |
| February 2021 | 0                     | 2                        |
| March 2021    | 1                     | 3                        |

WITH total_subscriptions AS (
    SELECT
        date_trunc('month', invoices.date) AS month,
        count(DISTINCT invoices.subscription_id) AS subscriptions,
        sum(amount_dollars) AS mrr
    FROM
        invoices
    GROUP BY
        1
),
churned_subscriptions AS (
    SELECT
        s.month,
        s.subscriptions,
        s.mrr,
        lag(subscriptions) OVER (ORDER BY s.month) AS last_month_subscriptions,
        count(DISTINCT CASE WHEN revenue_changes.change_type = 'removed' THEN
                revenue_changes.subscription_id
            END) AS churned_subscriptions
    FROM
        total_subscriptions s
        LEFT JOIN revenue_changes ON s.month = revenue_changes.month
    GROUP BY
        1,
        2,
        3
)
SELECT
    month,
    (mrr / subscriptions) AS arpc,
    (churned_subscriptions / last_month_subscriptions::float) AS subscription_churn_rate,
    (mrr / subscriptions) / (churned_subscriptions / last_month_subscriptions::float) AS ltv
FROM
    churned_subscriptions
WHERE
    month >= '2021-01-01'

| month         | MRR | subscription_total | ARPC | subscription_churn_rate | LTV   |
| ------------- | --- | ------------------ | ---- | ----------------------- | ----- |
| January 2021  | 250 | 2                  | 125  |                         |       |
| February 2021 | 450 | 3                  | 150  | 0.00                    |       |
| March 2021    | 300 | 2                  | 150  | 0.33                    | 454.5 |