WITH
    exp_users AS (
        SELECT
            ft.uid AS uid,
            ft.action,
            argMin(visitParamExtractString(ft.addJson, 'option'), dt) AS exp_group,
            min(dt) AS exp_dt
        FROM stat.funnelTrack AS ft
        WHERE ft.dt >= '2024-12-01'
          AND ft.action in ('pteam_appstat_v31', 'pteam_route_redesign')
        GROUP BY 1, 2
        HAVING exp_group IN ('old', 'new')
    ),
    contracts AS (
        SELECT
            uid,
            action,
            ContractID AS contract_id,
            argMin(Product, ContractCharge_TM) AS product,
            min(ContractCharge_TM) AS ts_charge
        FROM analytics.money JOIN exp_users using(uid)
        WHERE toTimeZone(ContractCharge_TM, 'GMT') >= '2023-01-01 00:00:00'
          AND toTimeZone(ActiveFrom, 'GMT') >= '2023-01-01 00:00:00'
          AND Type = 'subscription'
          AND RUBGross NOT IN (1, 11)
          AND ChargeNumber = 1
          AND USDGross >= 0.25
          AND BillingTypeGroup != 'partner'
          AND uid != ''
          AND ContractCharge_TM < exp_users.exp_dt
        GROUP BY uid, contract_id, action
    ),
    states AS (
        SELECT
            ContractID AS contract_id,
            State AS state,
            TM AS ts_state
        FROM analytics.ContractStateHistoryProduct
        WHERE toTimeZone(TM, 'GMT') >= '2023-01-01 00:00:00'
    ),
    user_states AS (
        SELECT
            c.uid,
            s.state,
            s.ts_state,
            c.ts_charge,
            c.action
        FROM contracts c
        INNER JOIN states s USING (contract_id)
    ),
    last_state_before AS (
        SELECT
            eu.uid as uid,
            action,
            argMaxIf(state, ts_state, ts_state < eu.exp_dt) AS last_state_before
        FROM user_states us
        INNER JOIN exp_users eu ON us.uid = eu.uid
        GROUP BY eu.uid, action
    ),
    last_state_after AS (
        SELECT
            eu.uid as uid,
            action,
            argMaxIf(state, ts_state, 1) AS last_state_after
        FROM user_states us
        INNER JOIN exp_users eu ON us.uid = eu.uid
        GROUP BY eu.uid, action
    )
SELECT
    eu.action,
    eu.exp_group,
    countDistinct(eu.uid) AS total_users,
    countDistinctIf(eu.uid, last_state_before IN ('ACTIVE', 'WAIT_CONFIRM', 'CONFIRMED')) AS active_before,
    countDistinctIf(eu.uid, last_state_after IN ('ACTIVE', 'WAIT_CONFIRM', 'CONFIRMED')) AS active_after,
    active_after / active_before AS alive_rate,
    active_before / total_users AS active_before_rate,
    active_after / total_users AS active_after_rate,
    (active_after - active_before) AS active_change_absolute,
    (active_after_rate - active_before_rate) AS active_change_rate
FROM exp_users eu
LEFT JOIN last_state_before lsb ON eu.uid = lsb.uid and eu.action = lsb.action
LEFT JOIN last_state_after lsa ON eu.uid = lsa.uid and eu.action = lsa.action
GROUP BY
    1, eu.exp_group
ORDER BY
    1, eu.exp_group;