Daily Subscription Revenue

Query

--Grab actual subscription revenue generated by date and subscription type

WITH daily_sub_revenue AS (
  SELECT
    convert_timezone('UTC','America/Los_Angeles',TO_TIMESTAMP(i.CREATED))::DATE AS date,
    s.PLAN['interval']::TEXT AS billing_interval,
    SUM(i.AMOUNT_PAID / 100.0) AS revenue
  FROM RAW_DB.STRIPE.INVOICES i
  JOIN RAW_DB.STRIPE.SUBSCRIPTIONS s
    ON i.SUBSCRIPTION = s.ID
  WHERE i.STATUS = 'paid'
    AND i.SUBSCRIPTION IS NOT NULL
    AND i.AMOUNT_PAID > 0
  GROUP BY 1, 2
)

SELECT
  id.dt,
  SUM(CASE WHEN dsr.billing_interval = 'month' THEN dsr.revenue ELSE 0 END) AS monthly_sub_revenue,
  SUM(CASE WHEN dsr.billing_interval = 'year'  THEN dsr.revenue ELSE 0 END) AS annual_sub_revenue
FROM 
    included_dates id
        left outer join daily_sub_revenue dsr on id.dt = dsr.date
GROUP BY ALL
ORDER BY id.dt;

Overview

Aggregates paid Stripe invoice amounts by date and billing interval, joined onto the date spine to produce a continuous daily revenue series. Only invoices with a paid status, a linked subscription, and a positive amount are included.

All timestamps are converted from UTC to America/Los_Angeles before date truncation.

Depends on: included_dates (date_spine)

---

Output Columns

Column	Description
dt	Calendar date
monthly_sub_revenue	Revenue from paid invoices on monthly (interval = 'month') subscriptions, in USD
annual_sub_revenue	Revenue from paid invoices on annual (interval = 'year') subscriptions, in USD

---

CTE

daily_sub_revenue

Sources: RAW_DB.STRIPE.INVOICES, RAW_DB.STRIPE.SUBSCRIPTIONS

Joins invoices to subscriptions to retrieve the billing interval, filters to paid invoices with a positive amount, and converts AMOUNT_PAID from cents to USD (/ 100.0). Groups by date and billing interval before the outer query pivots the result into separate columns.

---

Notes

• Revenue is recognized on the invoice payment date, not the subscription period start. Annual subscription revenue will therefore appear as a lump sum on the renewal date rather than spread across the year.
• Days with no paid invoices will return 0 for both revenue columns (due to SUM(... ELSE 0) rather than a left join with potential NULLs).
• Free plan subscriptions are excluded by the AMOUNT_PAID > 0 filter.
• Billing intervals outside 'month' and 'year' are silently excluded from both output columns.