-- A: Falhas de provedor por serviço
SELECT Provider,
    COUNTIF(Status = 'PROVIDER_FAILED') AS provider_failed,
    COUNT(*) AS total_execucoes,
    SAFE_DIVIDE(COUNTIF(Status = 'PROVIDER_FAILED'), COUNT(*)) AS pct_failed
FROM `ifood-prev-fraude-case.fraude_biometria.biometry_execution_typed`
GROUP BY Provider
ORDER BY pct_failed DESC,
    provider_failed DESC;

-- Taxa de falha total no processo de biometria
SELECT COUNT(*) AS total_sessoes,
    COUNTIF(
        COALESCE(Status_A, '') = 'PROVIDER_FAILED'
        AND COALESCE(Status_B, '') = 'PROVIDER_FAILED'
        AND COALESCE(Status_C, '') = 'PROVIDER_FAILED'
    ) AS sessoes_falhas,
    SAFE_DIVIDE(
        COUNTIF(
            COALESCE(Status_A, '') = 'PROVIDER_FAILED'
            AND COALESCE(Status_B, '') = 'PROVIDER_FAILED'
            AND COALESCE(Status_C, '') = 'PROVIDER_FAILED'
        ),
        COUNT(*)
    ) AS taxa_falha_total
FROM `ifood-prev-fraude-case.fraude_biometria.vw_biometry_base`;

-- Taxa de falha diária por provedor
SELECT DATE(Event_Dt) AS dt,
    Provider,
    COUNT(*) AS total_execucoes,
    COUNTIF(Status = 'PROVIDER_FAILED') AS falhas,
    SAFE_DIVIDE(COUNTIF(Status = 'PROVIDER_FAILED'), COUNT(*)) AS taxa_falha
FROM `ifood-prev-fraude-case.fraude_biometria.biometry_execution_typed`
WHERE Event_Dt IS NOT NULL
GROUP BY dt,
    Provider
ORDER BY dt,
    Provider;