gistfile1.txt

clicks by retrievers withn last xxx hours

select count(*),source from clicks,delivered_ads,ads where clicks.delivered_ad_id = delivered_ads.id and delivered_ads.ad_id = ads.id and clicked > date_add(now(), interval -168 HOUR) and clicks.scrubbed=0 group by ads.source;

clicks for shopping

select count(*) from clicks as c,delivered_ads as d,ads as a where c.delivered_ad_id = d.id and d.ad_id = a.id and clicked > date_add(now(), interval -48 HOUR) and c.scrubbed=0 and a.source = 'ShoppingAdsRetriever';

clicks for shopping for those with has_products

select count(distinct c.id) from clicks as c, delivered_ads as d, ads as a, responses as r, email_addresses as e, aggregation_data_emails as ade where c.delivered_ad_id = d.id and d.ad_id = a.id and d.response_id = r.id and r.email_address_id = e.id and e.address = ade.email and clicked > date_add(now(), interval -48 HOUR) and a.source = 'ShoppingAdsRetriever' and ade.has_products = 1 and scrubbed = 0;

impressions for shopping for those with has_products

select count(distinct o.id) from opens as o, delivered_ads as d, ads as a, responses as r, email_addresses as e, aggregation_data_emails as ade where o.response_id = r.id  and d.ad_id = a.id and d.response_id = r.id and r.email_address_id = e.id and e.address = ade.email and opened > date_add(now(), interval -48 HOUR) and a.source = 'ShoppingAdsRetriever' and ade.has_products = 1;