-- Survival analysis code reference from https://www.crosstab.io/articles/sql-survival-curves/

-- AVAILABLE FILTER VALUE 
-- Source:preset_custom_filter Column:day_use_to_churn
{% set day_use_to_churn = 61 %}

-- IMPORT
WITH companies AS (
    SELECT * FROM {{ ref('dim_exchange__companies') }} 
),

transaction_orders AS (
    SELECT * FROM {{ ref('fct_exchange__transaction_orders') }}
),


-- LOGIC
-- duration_table cte computes the survival time and customer churned indicator
duration_table AS (
 
    SELECT
        companies.company_id,                   --subject
        companies.company_created_datetime,     -- event start datetime
        COUNT(transaction_orders.order_id)                                                              AS count_order,
        COUNT(IFF(transaction_orders.order_status = 'COMPLETED', transaction_orders.order_id, null))    AS count_completed_order,
        MAX(transaction_orders.order_created_datetime)                                                  AS last_order_datetime,
        COALESCE(last_order_datetime, company_created_datetime)                                         AS last_activity_datetime,
        IFF(last_order_datetime IS NULL, 1, 0)                                                          AS is_never_order_company,

        -- if is_churn_company = 0, the data will be censored
        IFF(DATEDIFF(day, last_activity_datetime, CURRENT_DATE()) >= {{day_use_to_churn}}, 1, 0 )       AS is_churn_company,
        
        CASE 
            WHEN is_churn_company = 1 THEN
                DATEDIFF(day, companies.company_created_datetime, last_activity_datetime ) + {{day_use_to_churn}}
            ELSE
                DATEDIFF(day, companies.company_created_datetime, CURRENT_DATE())
        END     AS survival_time_days           -- event duration
    
    FROM companies
    
    LEFT JOIN transaction_orders
        ON (companies.company_id = transaction_orders.company_id)

    GROUP BY
        companies.company_id,
        companies.company_created_datetime

    HAVING
        -- companies without order will not be relevant to the analysis
        is_never_order_company = 0

),

-- the daily_tally cte count the total number of observations at each survival_time_day
-- and the number of company that have churned at that survival_time_day
daily_observation_tally AS (

    SELECT
        survival_time_days,
        COUNT(survival_time_days)   AS total_number_of_observations,
        SUM(is_churn_company)       AS events   -- considering only churned company

    FROM duration_table
    
    GROUP BY survival_time_days

    ORDER BY survival_time_days

),

-- the cumulative_tally cte counts the number of subjects still at risk of experiencing churn
cumulative_tally AS (

    SELECT
        survival_time_days,
        events,
        total_number_of_observations,
        
        ( SELECT COUNT( DISTINCT(company_id) ) FROM duration_table )    AS total_number_of_subjects,

        -- cumulative sum of observations at all previous survival_time_days SUBTRACTED by total_number_of_subjects
        total_number_of_subjects - COALESCE(SUM(total_number_of_observations) OVER (
            ORDER BY survival_time_days ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0
            )   AS at_risk

    FROM daily_observation_tally

),

-- At each survival_time_day, count number of censored subject
-- censored subjects = # subject at risk - churned - # subject at risk in the next duration
compute_censored_subjects AS (
    
    SELECT
        total_number_of_subjects,
        survival_time_days,
        at_risk,
        total_number_of_observations,
        events,
        at_risk - events - COALESCE(LEAD(at_risk, 1) OVER (ORDER BY survival_time_days), 0)     AS censored

    FROM cumulative_tally

    -- Simply subtracting events from number of observations would incorrectly ignore subjects censored at durations that are dropped from the output table
    WHERE events > 0

),

compute_probability AS (

    SELECT
        *,

        -- The survival probability represents the probability of customers that will not churn up to a specific tenure
        -- Example: survival_day = 61, survival_prob = 95%. For customers with 61 days of tenure, the customer has a 95% chance of not churning.
        -- When events / at_risk = 1, we replace value 1 with a value very near to 1, to prevent log 0 which causes infinity value
        EXP(SUM(LN
                (
                CASE WHEN (1 - events / at_risk) = 0 THEN 0.999999 ELSE (1 - events / at_risk) END
                )) 
            OVER (
                ORDER BY survival_time_days ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                ))      AS survival_probability,

        100 * (1 - EXP(SUM(LN
                (
                CASE WHEN (1 - events / at_risk) = 0 THEN 0.999999 ELSE (1 - events / at_risk) END
                )) 
            OVER (
                ORDER BY survival_time_days ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                )))     AS conversion_percentage,

        SUM(events / at_risk) 
            OVER (
            ORDER BY survival_time_days ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                 )      AS cumulative_hazard,

        '{{ modules.datetime.datetime.now(modules.pytz.timezone("Asia/Kuala_Lumpur")) }}'       AS _dbt_ran_datetime

    FROM compute_censored_subjects

),


-- FINAL
final__rep_exchange__survival_analysis AS (
    
    SELECT
        -- dimension
        survival_time_days,
        at_risk,
        total_number_of_observations,
        events,
        censored,

        -- measures
        survival_probability,
        conversion_percentage,
        cumulative_hazard,

        -- metadata
        _dbt_ran_datetime
    
    FROM compute_probability

)

SELECT * FROM final__rep_exchange__survival_analysis