QwNf9U2a

1create table if not exists hive.kaku.user_revenue_break_down_201707_2018_06 (
2    user_id bigint,
3    device_token varchar,
4    platform varchar,
5    install_date varchar,
6    seg varchar,
7    fq7 bigint,
8    fq28 bigint,
9    install_channel varchar,
10    gender varchar,
11    age varchar,
12    revenue_1d double,
13    revenue_28d double,
14    dt varchar
15)
16with (format = 'ORC')
17
18
19-- shell script
20
21start_date=20170701
22num_days=364
23for i in `seq 0 $num_days`
24do 
25date=`date +%Y-%m-%d -d "${start_date}+${i} days"`
26echo $date
27presto --server presto.smartnews.internal:8081 --execute "
28insert into hive.kaku.user_revenue_break_down_201707_2018_06 
29with dau as (
30    select user_id
31        ,device_token
32        ,platform
33        ,install_date
34        ,case when install_date = '$date' then 'new'
35            when install_date <> '$date' and fq28=1 then 'return'
36            else 'regular' end as seg
37        ,fq7
38        ,fq28
39    from hive.default.daily_user_fq_segment
40    where edition = 'ja_JP'
41    and fq1 = 1
42    and dt= '$date'
43),
44install_channel_info as (
45    select user_id
46        ,if(network_name = 'Organic','organic','paid') as install_channel
47    from hive.default.daily_user_installs
48    where user_id is not null
49    and device_token is not null
50    and dt = '$date'
51),
52revenue_1d as (
53    select device_token
54        ,dt
55        ,sum(revenue_e6) / 1000000.0 as revenue_1d
56    from hive_ad.kpi.daily_user_revenue
57    where dt = '$date'
58    and revenue_e6 <> 0
59    group by 1,2
60),
61revenue_28d as (
62    select device_token
63        ,sum(revenue_e6) / 1000000.0 as revenue_28d
64    from hive_ad.kpi.daily_user_revenue
65    where dt between '$date' and cast(date_add('day', 27, cast('$date' as date)) as varchar)
66    and revenue_e6 <> 0
67    group by 1
68)
69select a.*
70    ,coalesce(d.install_channel,'unknown') as install_channel
71    ,case when substr(e.gender,1,1) = 'M' then 'male'
72        when substr(e.gender,1,1) = 'F' then 'female'
73        else 'unknown' end as gender
74    ,if(e.sn_age is null, 'unknown', cast(e.sn_age as varchar)) as age
75    ,coalesce(b.revenue_1d,0) as revenue_1d
76    ,coalesce(c.revenue_28d,0) as revenue_28d
77    ,'$date' dt
78from dau a
79left join revenue_1d b 
80on a.device_token = b.device_token
81left join revenue_28d c
82on a.device_token = c.device_token
83left join install_channel_info d
84on a.user_id = d.user_id
85left join hive_ad.default.ad_audience_v2 e
86on a.user_id = e.sn_user_id and e.dt='$date'
87group by 1,2,3,4,5,6,7,8,9,10,11,12
88"
89done