stripe_invoices_transformed_4.sql

select date_month, d.customer, period_start, period_end,
  "interval" as period,
  case "interval"
    when 'yearly'
      then coalesce(i.total, 0)::float / 12 / 100
    else
      coalesce(i.total, 0)::float / 100
  end as total,
  case min(date_month) over(partition by d.customer)
    when date_month then 1
    else 0
    end as first_payment,
  case max(date_month) over(partition by d.customer)
    when date_month then 1
    else 0
  end as last_payment
from customer_dates d
  left outer join invoices i
    on d.date_month >= date_trunc('month', i.period_start)
    and d.date_month < date_trunc('month', i.period_end)
    and d.customer = i.customer
  left outer join {{env.schema}}.stripe_subscriptions s on i.subscription_id = s.id
  left outer join {{env.schema}}.stripe_plans p on s.plan_id = p.id