ml_model_analysis_v2.sql

#standardsql

WITH all_spams_fides AS (
   SELECT  DISTINCT requestId, 
         FIRST_VALUE(vrd.score) OVER (PARTITION BY requestId ORDER BY vrd.score DESC) as score,
         is_admin_deleted
  FROM mi.fides_events_nrt n, UNNEST(verifierData) as vrd
  INNER JOIN a.mts_requests r ON r.request_id = requestId
  WHERE (n._PARTITIONTIME >= '2017-09-21' OR n._PARTITIONTIME IS NULL)
  AND time > 1506034800000
  AND r.create_time >= '2017-09-21' 
  AND vrd.name = "request.baseline"
  AND (vrd.outcome = 2 OR vrd.outcome = 0)
),

all_spams_ml AS (
  SELECT DISTINCT requestId, 
         FIRST_VALUE(vrd.score) OVER (PARTITION BY requestId ORDER BY vrd.score DESC) as score,
         is_admin_deleted
  FROM mi.fides_events_nrt n, UNNEST(verifierData) as vrd
  INNER JOIN a.mts_requests r ON r.request_id = requestId
  WHERE (n._PARTITIONTIME >= '2017-09-21' OR n._PARTITIONTIME IS NULL)
    AND time > 1506034800000
    AND r.create_time >= '2017-09-21' 
    AND vrd.name = "request.ml_spam_verifier"
    AND score > 0.431
)

SELECT ml.requestId, ml.score, ml.is_admin_deleted
FROM all_spams_ml ml
LEFT OUTER JOIN all_spams_fides f ON ml.requestId = f.requestId 
WHERE f.requestId IS NULL --AND (ml.is_admin_deleted) --378
ORDER BY 2