jthandy

def push_to_warehouse(table, data_to_push)

  	response = HTTParty.post("https://connect.rjmetrics.com/v1/client/#{cid}/table/#{table}/data?apikey=#{apikey}", 
		body: 		data_to_push.to_json,
		headers: 	{ 'Content-Type' => 'application/json' })

	response

end

jthandy

Verifying that +jthandy is my blockchain ID. https://onename.com/jthandy

jthandy

select
  customer,
  timestamp 'epoch' + date * interval '1 Second' as date,
  forgiven,
  subscription_id,
  paid,
  total,
  timestamp 'epoch' + period_start * interval '1 Second' as period_start,
  timestamp 'epoch' + period_end * interval '1 Second' as period_end

jthandy

with invoices as (

  select *
  from {{env.schema}}.stripe_invoices_cleaned
  where paid is true
    and forgiven is false

),

jthandy

days as (

  select (min(period_start) over () + row_number() over ())::date as date_day
  from invoices

), months as (

  select distinct date_trunc('month', date_day)::date as date_month
  from days
  where date_day <= current_date

jthandy

customers as (

  select customer, min(period_start) as active_from, max(period_end) as active_to
  from invoices
  where period_start <= current_date
  group by customer

), customer_dates as (

  select m.date_month, c.customer

jthandy

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)

jthandy

with invoices as (

  select *
  from {{env.schema}}.stripe_invoices_transformed

), all_months as (

  select distinct date_month from invoices

),

jthandy

plan_changes as (

  select
    *,
    lag(total) over (partition by customer order by date_month) as prior_month_total,
    total - coalesce(lag(total) over (partition by customer order by date_month), 0) as change,
    lag(period_end) over (partition by customer order by date_month) as prior_month_period_end
  from invoices

),

jthandy

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