unload select did zone_category app_category zone_country zone_id avg_

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
unload (
'select did,zone_category,app_category,zone_country,zone_id,avg_imps,avg_clicks,avg_installs,zone_ecpm,rating,downloads,ins_sid,last_imp_date,imps_count,ratings_1,ratings_2,
ratings_3,ratings_4,ratings_5,screenshots_count,active_days,avg_user_zones,avg_user_sids,avg_users_camps,tid,clicked_days,
avg_user_clicks,avg_user_clk_bn_cnt,is_install
from
(select aa.did,
zone_params.zone_category,
ac.category as app_category,
aa.country as zone_country,
aa.zone_id,
avg_imps,
avg_clicks,
avg_installs,
zone_params.ecpm as zone_ecpm,
ai.rating,
ai.downloads,
ai.ratings_1,
ai.ratings_2,
ai.ratings_3,
ai.ratings_4,
ai.ratings_5,
ai.screenshots_count,
active_days,
avg_user_zones,
avg_user_sids,
avg_users_camps,
tid,
clicked_days,
avg_user_clicks,
avg_user_clk_bn_cnt,
insts.sid as ins_sid,
case when insts.package is not null then 1 else 0 end as is_install,
max(aa.set_date) as last_imp_date,
count(*) as imps_count
from spectrum.impressions_parq aa
join public.campaign c on c.cm_campaignid=aa.campaign_id
join public.publisher_app pa on pa.pba_id=c.pba_id
join public.apps_categories_all ac on ac.package = pa.pba_url1
join (select it.embedid,count(*) from installs_tracking it where it.setdate >= \'2019-12-01\' and it.setdate < \'2019-12-08\'
group by embedid having count(*) > 10) good_zones on aa.zone_id = good_zones.embedid
left join public.app_info ai on ai.package = pa.pba_url1
left join (select outer_aggs.zone_id,outer_aggs.zone_category,outer_aggs.country_code,avg(outer_aggs.impressions) as avg_imps,avg(outer_aggs.clicks) as avg_clicks,
avg(outer_aggs.installs) as avg_installs,
case when sum(outer_aggs.impressions) > 0 then ((sum(nvl(outer_aggs.advertiser_spend::float,0.0)) + sum(nvl(outer_aggs.clicks_revenue::float,0.0)))/sum(outer_aggs.impressions::float))*1000
else 0 end as ecpm,
case when sum(outer_aggs.impressions) > 0 then sum(outer_aggs.installs::float)/sum(outer_aggs.impressions::float)
else 0 end as cr
from
(select "date",zone_id,ac.category as zone_category,aggs.country_code,
nvl(sum(impressions),0) as impressions,nvl(sum(regular_clicks),0) as clicks,nvl(sum(total_installs),0) as installs,nvl(sum(advertiser_spend),0) as advertiser_spend,
nvl(sum(clicks_revenue),0) as clicks_revenue
from public.imp_click_ins_agg aggs
join public.platform p on aggs.zone_id = p.pl_zoneid
join public.publisher_app pa on pa.pba_id=p.pba_id
left join public.apps_categories_all ac on ac.package = pa.pba_url1
where aggs.date >= \'2019-12-01\' and aggs.date < \'2019-12-08\'
group by 1,2,3,4) outer_aggs
group by outer_aggs.zone_id,outer_aggs.zone_category,outer_aggs.country_code) zone_params on aa.zone_id = zone_params.zone_id and aa.country = zone_params.country_code
left join (select it.deviceid as did,pa.pba_url1 as package,ins.sid as sid,ins.banner_id from installs_tracking it
join public.publisher_app pa on pa.pba_id=it.pubapp_of_creative_id
join public.installs_fh ins on it.guid = ins.click_id
where it.setdate >= \'2019-12-01\' and it.setdate < \'2019-12-08\' and ins.cre_date >= \'2019-12-01\' and ins.cre_date < \'2019-12-08\') insts on aa.did = insts.did and aa.sid = insts.sid and aa.banner_id = insts.banner_id
where aa.did is not null and aa.did !=\'00000000-0000-0000-0000-000000000000\'
and aa.ts >= 20191201 and aa.ts < 20191208
and aa.error_response = 4 and c.cm_type = 3 and c.pl_type = \'android\'
and aa.zone_id IN (1020071,1021860,1023343,1021863,1020076,1019254,1020116,1020121,1019250,1019253,1021501,1019249,1019251,1020075,1020119,1021862,1019252,1020070,1020073,1020074,1020114,1018975,1020117,1020072,1020118,1020120,1021861,1020111,1019255)
group by aa.did,zone_params.zone_category,ac.category,aa.country,aa.zone_id,avg_imps,avg_clicks,avg_installs,zone_params.ecpm,ai.rating,ai.downloads,ai.ratings_1,ai.ratings_2,ai.ratings_3,ai.ratings_4,ai.ratings_5,ai.screenshots_count,ins_sid,active_days,avg_user_zones,avg_user_sids,avg_users_camps,tid,clicked_days,
avg_user_clicks,avg_user_clk_bn_cnt,is_install) last')
TO 's3://appnext-datalake/csv/zones_data_imps_general/imps.csv'
iam_role 'arn:aws:iam::256315800822:role/redshift-s3'
delimiter ','
ALLOWOVERWRITE
ADDQUOTES;