-- E2: Correlação entre dia da semana e taxa de NOT_MATCH
WITH diario AS (
  SELECT DATE(Event_Dt) AS dt,
    EXTRACT(
      DAYOFWEEK
      FROM Event_Dt
    ) AS dow, -- 1 = segunda, 7 = domingo
    COUNT(*) AS sessoes,
    COUNTIF(Final_Status = 'NOT_MATCH') AS not_match
  FROM `ifood-prev-fraude-case.fraude_biometria.vw_biometry_enriched`
  WHERE Event_Dt IS NOT NULL
  GROUP BY dt,
    dow
  HAVING sessoes >= 10
),
taxa_por_dia_semana AS (
  SELECT dow,
    ROUND(AVG(SAFE_DIVIDE(not_match, sessoes)), 4) AS not_match_rate_media,
    COUNT(*) AS dias_agrupados
  FROM diario
  GROUP BY dow
)
SELECT dow,
  CASE
    dow
    WHEN 1 THEN 'Segunda'
    WHEN 2 THEN 'Terça'
    WHEN 3 THEN 'Quarta'
    WHEN 4 THEN 'Quinta'
    WHEN 5 THEN 'Sexta'
    WHEN 6 THEN 'Sábado'
    WHEN 7 THEN 'Domingo'
  END AS dia_semana,
  not_match_rate_media,
  dias_agrupados,
  CORR(dow, not_match_rate_media) OVER() AS corr_dia_notmatch
FROM taxa_por_dia_semana
ORDER BY dow;