stripe_analysis_3.sql

data as (

  select *,
    case
      when first_payment = 1
        then 'new'
      when last_payment = 1
        and dateadd('month', 1, period_end) < current_date
        then 'churn'
      when change > 0
        then 'upgrade'
      when change < 0
        then 'downgrade'
      when period != 'monthly'
        and date_month < date_trunc('month', prior_month_period_end)
        then 'prepaid renewal'
      else
        'renewal'
      end revenue_category,
      case
        when prior_month_total < total then prior_month_total
        else total
      end renewal_component_of_change
  from plan_changes

),