gareginordyan

-- find all jobs with prod target name by dbt
with jobs_with_dbt_prod_target as (
    select distinct
      job_id
    from `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
    where query like '%"target_name": "prod"%'
--find all tables/views that were created (or refreshed) in the last 5 days 
), recent_tables_in_prod_by_dbt_transform_layer as (
  select distinct
    concat(destination_table.project_id, '.', destination_table.dataset_id, '.', destination_table.table_id) as pkey,

gareginordyan

with interview_events as (
    select 
        scheduled_interview.id as sch_interview_id, 
        scheduled_interview.application_id,
        date(scheduled_interview.start) as activity_date, 
        interview.name as interview_name,
        case
            when lower(job_stage.name) = 'initial screen' then 'Initial Screen'
            when lower(job_stage.name) = 'second screen' then 'Second Screen'
            when lower(job_stage.name) like '%screen%' then 'Other Screen'

gareginordyan

with inputs as (
  select
    account_id as fivetran_account_id,
    month,
    connector as excess_service,
    excess_mar as excess_total_mar
  from scratch.credit_giveback_qa_input
), pull_credit_and_mar_data as (
  select
    mar.account_id,

gareginordyan

--create or replace table scratch.credit_consumption as
with pct_time_customer as (
  select
    account_id,
    date_trunc(timeline_date, month) as month,
    round(
      sum(
        case when status = 'Customer' then 1 else 0 end 
        / date_diff(date_add(date_trunc(timeline_date, month), interval 1 month), date_trunc(timeline_date, month), day)
      ),

gareginordyan

-- 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

gareginordyan

create or replace table tmp_sla_analysis.combined_arr_3_mo as
WITH revenue AS (with churn_flag_components as (
        select
          * except(days_delta),
          -- if we have new_business, then it is one new customer, if we gave a reactivation event, then it's a new customer
          if(new_business_legacy <> 0, 1, 0) as new_cust_count,
          -- a customer is considered lost it it does not comeback in the next 180 days
          if((churn_legacy <> 0 and arr_legacy = 0 and (lead(days_delta) over account_to_this_day > 180))
            or lead(days_delta) over account_to_this_day is null, 1, 0) as lost_cust_count
        from `digital-arbor-400.looker_pdts.LR_CQM6Z1597852267935_z_dt_churn_logic`

gareginordyan

import requests, json, math, datetime

# global variables
api_base_url = 'https://fivetran.namely.com/api/v1'
response_per_page = 20

def handler(request):
    # get the credentials needed securely from Fivetran function // no storing credentials in code
    request_json = request.get_json(silent=True)
    bearer_token = request_json['secrets']['bearer_token']