-- D2: Média da taxa de cancelamento por grupo de biometria
WITH sess_por_driver AS (
  SELECT Driver_ID,
    COUNT(*) AS sessoes,
    COUNTIF(Final_Status = 'MATCH') AS qtd_match,
    COUNTIF(Final_Status = 'NOT_MATCH') AS qtd_not_match
  FROM `ifood-prev-fraude-case.fraude_biometria.vw_biometry_enriched`
  WHERE Driver_ID IS NOT NULL
  GROUP BY Driver_ID
),
canc_por_driver AS (
  SELECT Driver_ID,
    SUM(Total_Orders) AS orders_total,
    SUM(Cancels) AS cancels_total,
    SAFE_DIVIDE(SUM(Cancels), NULLIF(SUM(Total_Orders), 0)) AS cancel_rate
  FROM `ifood-prev-fraude-case.fraude_biometria.vw_orders_daily`
  WHERE Driver_ID IS NOT NULL
  GROUP BY Driver_ID
),
joined AS (
  SELECT s.Driver_ID,
    SAFE_DIVIDE(s.qtd_not_match, s.sessoes) AS not_match_rate,
    c.cancel_rate
  FROM sess_por_driver s
    LEFT JOIN canc_por_driver c USING (Driver_ID)
  WHERE c.cancel_rate IS NOT NULL
)
SELECT CASE
    WHEN not_match_rate = 1 THEN 'Somente NOT_MATCH'
    WHEN not_match_rate = 0 THEN 'Somente MATCH'
    ELSE 'Misto'
  END AS grupo_biometria,
  COUNT(*) AS qtd_drivers,
  ROUND(AVG(cancel_rate), 4) AS media_cancel_rate
FROM joined
GROUP BY grupo_biometria;

-- Correlação entre status biometria e taxa de cancelamento
WITH por_driver AS (
  SELECT Driver_ID,
    SAFE_DIVIDE(COUNTIF(Final_Status = 'NOT_MATCH'), COUNT(*)) AS not_match_rate
  FROM `ifood-prev-fraude-case.fraude_biometria.vw_biometry_enriched`
  WHERE Final_Status IN ('MATCH', 'NOT_MATCH')
  GROUP BY Driver_ID
),
canc_por_driver AS (
  SELECT Driver_ID,
    SAFE_DIVIDE(SUM(Cancels), NULLIF(SUM(Total_Orders), 0)) AS cancel_rate
  FROM `ifood-prev-fraude-case.fraude_biometria.vw_orders_daily`
  WHERE Driver_ID IS NOT NULL
  GROUP BY Driver_ID
)
SELECT CORR(p.not_match_rate, c.cancel_rate) AS correlacao
FROM por_driver p
  LEFT JOIN canc_por_driver c USING (Driver_ID);

-- Teste t para diferença de médias da taxa de cancelamento entre grupos MATCH e NOT_MATCH
WITH por_driver AS (
  SELECT Driver_ID,
    SAFE_DIVIDE(SUM(Cancels), NULLIF(SUM(Total_Orders), 0)) AS cancel_rate,
    CASE
      WHEN Final_Status = 'NOT_MATCH' THEN 'NOT_MATCH'
      WHEN Final_Status = 'MATCH' THEN 'MATCH'
      ELSE NULL
    END AS grupo
  FROM `ifood-prev-fraude-case.fraude_biometria.vw_biometry_enriched` b
    JOIN `ifood-prev-fraude-case.fraude_biometria.vw_orders_daily` o USING (Driver_ID)
  WHERE Final_Status IN ('MATCH', 'NOT_MATCH')
  GROUP BY Driver_ID,
    grupo
),
-- Estatísticas por grupo (MATCH vs NOT_MATCH)
stats AS (
  SELECT grupo,
    COUNT(*) AS n,
    AVG(cancel_rate) AS mean_cancel,
    STDDEV_POP(cancel_rate) AS std_cancel
  FROM por_driver
  GROUP BY grupo
),
-- Combina estatísticas em uma única linha
combined AS (
  SELECT MAX(
      CASE
        WHEN grupo = 'MATCH' THEN n
      END
    ) AS n1,
    MAX(
      CASE
        WHEN grupo = 'MATCH' THEN mean_cancel
      END
    ) AS mean1,
    MAX(
      CASE
        WHEN grupo = 'MATCH' THEN std_cancel
      END
    ) AS std1,
    MAX(
      CASE
        WHEN grupo = 'NOT_MATCH' THEN n
      END
    ) AS n2,
    MAX(
      CASE
        WHEN grupo = 'NOT_MATCH' THEN mean_cancel
      END
    ) AS mean2,
    MAX(
      CASE
        WHEN grupo = 'NOT_MATCH' THEN std_cancel
      END
    ) AS std2
  FROM stats
)
-- Cálculo do t-statisticic
SELECT *,
  (mean1 - mean2) AS diff_means,
  SQRT(POW(std1, 2) / n1 + POW(std2, 2) / n2) AS se_diff,
  SAFE_DIVIDE(
    (mean1 - mean2),
    SQRT(POW(std1, 2) / n1 + POW(std2, 2) / n2)
  ) AS t_stat
FROM combined;