an enhanced version of the credit consumption query

credit_consumption_2

-- create or replace table scratch.credit_consumption as
with identify_credit_type_to_use as (
  select
  *,
  case
    -- when count of trials > count of non-cbp customer records then this is a new cbp customer and we should use credits instead of assumed_credits
    when (count(case when status like 'Trial%' then 1 else null end) over account_month) >= count(case when status = 'Customer' and coalesce(platform_tier, '') not like 'Usage%' then 1 else null end) over account_month 
      and count(case when status = 'Customer' and coalesce(platform_tier, '') like 'Usage%' then 1 else null end) over account_month > 0
      then true
    else false
  end as use_actual_credits_over_assumed
from transforms.daily_credit_usage
window account_month as (partition by account_id, month)
order by account_id, date
)
select
account_id,
month,
sum(
  coalesce(
    case
      when status = 'Customer' and use_actual_credits_over_assumed then credits_used
      when status = 'Customer' and not use_actual_credits_over_assumed then assumed_credits
      else 0
    end,
    0
  )
) as credits
from identify_credit_type_to_use
group by 1, 2
having credits > 0