tkdm_data_retention_summary_day.sql 1.27 KB
insert overwrite table tkdm.tkdm_data_retention_summary_day partition(ds='2017-02-28') 

select /*+ mapjoin(b) */ a.appid,
       a.cid,
       date_sub('2017-02-28',1) as ds_user,
       a.num_user,
       a.d1_user,
       a.d1_rate,
       b.category_id,
       b.category_name,
       b.isgame
from 
(select appid,
        cid,
        sum(num_install) as num_user,
        sum(num_newuser_reten_d1) as d1_user,
        round(sum(num_newuser_reten_d1)/sum(num_install),4) as d1_rate
     from tkio.tkio_rpt_channel_compare_day
     where ds between date_sub('2017-02-28',1) and  '2017-02-28' and 
          dt=date_sub('2017-02-28',1) and 
           campaignid !='_default_' and 
           cid!=-2
     group by appid,cid
     having sum(num_install)>0
) as a 

join 

(select appid,
        category_id,
        category_name,
        isgame
     from tkio_v2.tkio_dp_app_info
     where ds =date_sub('2017-02-28',1)
) as b on a.appid=b.appid




create EXTERNAL table tkdm_data_retention_summary_day(
appid string,
cid int,
ds_user string,
num_user int,
d1_user int,
d1_rate double,
category_id int,
category_name string,
isgame int
)
PARTITIONED BY (ds string)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
location 's3://reyuntkio/warehouse/tkio/tkdm.db/tkdm_data_retention_summary_day'
;