-- B: NOT_MATCH + taxa de cancelamento + média de devices + MEDIANA de dias desde o registro
-- NOT_MATCH: sessões que falharam na biometria
WITH base_biometry AS (
    SELECT Category,
        Driver_ID,
        COUNT(*) AS total_sessoes,
        COUNTIF(Final_Status = 'NOT_MATCH') AS not_match
    FROM `ifood-prev-fraude-case.fraude_biometria.vw_biometry_enriched`
    GROUP BY Category,
        Driver_ID
),
-- cancelamentos por driver (total e taxa)
base_orders AS (
    SELECT Driver_ID,
        SUM(Total_Orders) AS total_orders,
        SUM(Cancels) AS cancels,
        SAFE_DIVIDE(SUM(Cancels), NULLIF(SUM(Total_Orders), 0)) AS cancel_rate
    FROM `ifood-prev-fraude-case.fraude_biometria.vw_orders_daily`
    GROUP BY Driver_ID
),
-- quantidade média de devices por driver
base_devices AS (
    SELECT Driver_ID,
        COUNT(DISTINCT Device_ID) AS qtd_devices
    FROM `ifood-prev-fraude-case.fraude_biometria.vw_biometry_enriched`
    WHERE Device_ID IS NOT NULL
    GROUP BY Driver_ID
),
-- mediana por driver dos dias desde o registro até as sessões
tempo_driver AS (
    SELECT Category,
        Driver_ID,
        APPROX_QUANTILES(
            DATE_DIFF(DATE(Event_Dt), DATE(Register_Dt), DAY),
            100
        ) [OFFSET(50)] AS median_days_since_register_driver
    FROM `ifood-prev-fraude-case.fraude_biometria.vw_biometry_enriched`
    WHERE Register_Dt IS NOT NULL
        AND Event_Dt IS NOT NULL
    GROUP BY Category,
        Driver_ID
),
-- mediana tempo de registro por categoria (entre os drivers da categoria)
tempo_categoria AS (
    SELECT Category,
        APPROX_QUANTILES(median_days_since_register_driver, 100) [OFFSET(50)] AS median_days_since_register
    FROM tempo_driver
    GROUP BY Category
)
SELECT b.Category,
    -- biometria
    SUM(b.total_sessoes) AS total_sessoes,
    SUM(b.not_match) AS not_match,
    SAFE_DIVIDE(SUM(b.not_match), SUM(b.total_sessoes)) AS not_match_rate,
    -- cancelamentos
    ROUND(AVG(o.cancel_rate), 4) AS avg_cancel_rate,
    -- devices
    ROUND(AVG(d.qtd_devices), 2) AS avg_devices_per_driver,
    -- mediana de dias desde o registro (por categoria)
    tempo.median_days_since_register
FROM base_biometry b
    LEFT JOIN base_orders o USING (Driver_ID)
    LEFT JOIN base_devices d USING (Driver_ID)
    LEFT JOIN tempo_categoria tempo USING (Category)
GROUP BY b.Category,
    tempo.median_days_since_register
ORDER BY not_match_rate DESC,
    avg_cancel_rate DESC;