Cacher is the code snippet organizer for pro developers

SELECT
   a.pubid,
   round(avg(perc_sad),2) as avg_f,
   max(perc_sad) as max_f,
   --max(case when perc_sad >= a.fraud_threshold then day else '0' end) as last_fraud_day,
   datediff(now(),max(case when perc_sad >= a.fraud_threshold then day else '0' end)) as days_f,
   sum(case when perc_sad >= a.fraud_threshold then 1 else 0 end) as f_days,
   sum(case when total >= 200 then 1 else 0 end) as ss_days,
   count(*) as t_days 
FROM
       (SELECT
           day,
           extpublisherid as pubid,
           round((sum(cast(cast(sadscore as float) as int))/count(*))*100,2) as perc_sad,
           4 as fraud_threshold,
           count(*) as total
       FROM
           thirdparty.iasdata
       WHERE extpublisherid = '561409'
       AND day between to_date(date_sub(now(),30)) and to_date(date_sub(now(),1))
       group by 1,2) a      
group by 1