-- D1: visão por driver: sessões de biometria + pedidos + taxa de cancelamento (explorada, mas não utilizada nos slides)
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
)
SELECT s.Driver_ID,
  s.sessoes,
  SAFE_DIVIDE(s.qtd_not_match, s.sessoes) AS not_match_rate,
  c.orders_total,
  c.cancels_total,
  c.cancel_rate
FROM sess_por_driver s
  LEFT JOIN canc_por_driver c USING (Driver_ID)
ORDER BY not_match_rate DESC,
  cancel_rate DESC;