#standardsql
SELECT
  is_spam,
  IF(reason_code LIKE '%3 Local Persona Variations per Day%', 1, 0) AS three_local_persona_variations,
  COUNT(request_id) as requests
FROM
    (
      SELECT
        is_spam,
        REPLACE(MAX(IF(z.key = 'reason_code' , z.val, NULL)),'"','') AS reason_code,
        request_id
      FROM
          (
            SELECT DISTINCT
              request_id,
              mr.category,
              mr.is_spam,
              key as key,
              value as val
            FROM mi.fides_events f, f.kvPairString kv1
            LEFT JOIN a.mts_requests mr
              ON mr.request_id = f.requestId
            WHERE name = 'request verification rule/tmx_result'
            AND DATE(_PARTITIONTIME) >= DATE_SUB(CURRENT_DATE(), INTERVAL 104 DAY)
            AND DATE(_PARTITIONTIME) < DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY)
          )z
      GROUP BY 1,3
    )y
GROUP BY 1,2