with data_clics as (
        select clic_site, clic_id, clic_internaute, clic_ip, kit_mailing_lien_kit_mailing -- , clic_programme
        from affiliation.clic c
        inner join affiliation.kit_mailing_lien on kit_mailing_lien_lien = clic_lien -- juste pour emailing
        where clic_date_epoch >= :mois_debut -- debut mois
        and clic_date_epoch < :mois_fin -- fin mois
	and clic_date_epoch >= 1448924400 -- on se positionne a 01/12/2015 (pour que les nouvelles stats ne soient applicable qu'à partir de ce moment la)
        -- and clic_programme = 322
),
data_rejected as (
        select 
                clic_site, 
                kit_mailing_lien_kit_mailing,
                array_agg(clic_id) as clics,
                -- clic_ip
                count(*) as total
        from data_clics
        group by clic_site, kit_mailing_lien_kit_mailing, clic_ip
        having count(*) / count(distinct kit_mailing_lien_kit_mailing) > 4

        union

        select 
                clic_site,     
                kit_mailing_lien_kit_mailing,            
                array_agg(clic_id) as clics,
                -- clic_internaute, 
                count(*) as total
        from data_clics
        group by kit_mailing_lien_kit_mailing, clic_site, clic_internaute
        having count(*) / count(distinct kit_mailing_lien_kit_mailing) > 4
),
clics_rejected as (
        select distinct unnest(clics) as clic_rejected
        from data_rejected
)
-- select distinct unnest(clics) from data_rejected
update affiliation.clic
set clic_valide = 0
from clics_rejected
where clic.clic_id = clics_rejected.clic_rejected
and clic_valide = 1
and clic_date_epoch >= :periode_debut  -- debut periode de travail
and clic_date_epoch <  :periode_fin  -- debut periode de travail
and clic_date_epoch >= 1448924400 -- on se positionne a 01/12/2015 (pour que les nouvelles stats ne soient applicable qu'à partir de ce moment la)