编辑代码

-- r_app_info_dt 
-- dwd_app_active_log_d

-- 新增店铺数
select count(distinct app_id) new_business_college_shop,
'${statdate}' statdate
from db_result.r_app_info_dt
where statdate = '${statdate}' and app_created_at like '${stat_date}%'
and app_type = 2;


-- 新增付费店铺苏
select count(distinct app_id) new_business_college_formal_shop,
'${statdate}' statdate
from db_result.r_app_info_dt
where statdate = '${statdate}' and version_type = 171
and app_created_at like '${stat_date}%';

-- 活跃店铺数

select count(distinct a.app_id) active_business_college_shop,
'${statdate}' statdate
from
(select app_id
from db_result.r_app_info_dt
where statdate = '${statdate}'
and app_type = 2) a
left join 
(SELECT app_id
FROM db_middle.dwd_app_active_log_d
WHERE statdate = '${statdate}'
AND created_at LIKE '${stat_date}%') b
on a.app_id = b.app_id 
where b.app_id is not null;

活跃付费店铺数
select count(distinct c.app_id) active_business_college_formal_shop,
'${statdate}' statdate
from
(select app_id
from db_result.r_app_info_dt
where statdate = '${statdate}'
and version_type = 171) c
left join
(SELECT app_id
FROM db_middle.dwd_app_active_log_d
WHERE statdate = '${statdate}'
AND created_at LIKE '${stat_date}%') d
on c.app_id = d.app_id
where d.app_id is not null;

-- 累计店铺数
select count(distinct app_id) total_business_college_shop,
'${statdate}' statdate
from db_result.r_app_info_dt
where statdate = '${statdate}'
and app_type = 2;

-- 累计付费店铺数
select count(distinct app_id) total_business_college_formal_shop,
'${statdate}' statdate
from db_result.r_app_info_dt
where statdate = '${statdate}' and version_type = 171;

-- C端
-- 新增用户
select count(distinct user_id) new_business_college_mandate_user,
'${statdate}' statdate
from db_source.s_db_wework_t_employees_dt
where statdate = '${statdate}'
and is_deleted = 0
and created_at like '${stat_date}%'


-- 活跃用户
select count(distinct f.user_id) active_business_college_shop_user,
'${statdate}' statdate
from 
(
    select app_id
from db_result.r_app_info_dt
where statdate = '${statdate}'
and app_type = 2 
) e
left join
(
    select app_id, user_id
    from db_middle.m_c_report_log_d
    where statdate = '${statdate}'
    and visited_at like '${stat_date}'
) f
on e.app_id = f.app_id
where f.app_id is not null; 

-- 累计用户

select count(distinct h.user_id) total_business_college_mandate_user,
'${statdate}' statdate
from
(
     select app_id
from db_result.r_app_info_dt
where statdate = '${statdate}'
and app_type = 2
) g
left join
(
    select app_id, user_id
from db_source.s_db_wework_t_employees_dt
where statdate = '${statdate}'
and is_deleted = 0
) h
on g.app_id = h.app_id
where h.app_id is not null;


-- 企业培训

select '$stat_date' count_date,
coalesce(t1.new_business_college_shop, 0) new_business_college_shop,
coalesce(t2.new_business_college_formal_shop, 0) new_business_college_formal_shop,
coalesce(t3.active_business_college_shop, 0) active_business_college_shop,
coalesce(t4.active_business_college_formal_shop, 0) active_business_college_formal_shop,
coalesce(t5.total_business_college_shop, 0) total_business_college_shop,
coalesce(t6.total_business_college_formal_shop, 0) total_business_college_formal_shop,
coalesce(t7.new_business_college_mandate_user, 0) new_business_college_mandate_user,
coalesce(t8.active_business_college_shop_user, 0) active_business_college_shop_user,
coalesce(t9.total_business_college_mandate_user, 0) total_business_college_mandate_user
from
(select count(distinct app_id) new_business_college_shop,
'${statdate}' statdate
from db_result.r_app_info_dt
where statdate = '${statdate}' and app_created_at like '${stat_date}%'
and app_type = 2) t1
left join
(select count(distinct app_id) new_business_college_formal_shop,
'${statdate}' statdate
from db_result.r_app_info_dt
where statdate = '${statdate}' and version_type = 171
and app_created_at like '${stat_date}%') t2 on t1.statdate = t2.statdate
left join
(select count(distinct a.app_id) active_business_college_shop,
'${statdate}' statdate
from
(select app_id
from db_result.r_app_info_dt
where statdate = '${statdate}'
and app_type = 2) a
left join 
(SELECT app_id
FROM db_middle.dwd_app_active_log_d
WHERE statdate = '${statdate}'
AND created_at LIKE '${stat_date}%') b
on a.app_id = b.app_id 
where b.app_id is not null) t3 on t1.statdate = t3.statdate
left join
(select count(distinct c.app_id) active_business_college_formal_shop,
'${statdate}' statdate
from
(select app_id
from db_result.r_app_info_dt
where statdate = '${statdate}'
and version_type = 171) c
left join
(SELECT app_id
FROM db_middle.dwd_app_active_log_d
WHERE statdate = '${statdate}'
AND created_at LIKE '${stat_date}%') d
on c.app_id = d.app_id
where d.app_id is not null) t4 on t1.statdate = t4.statdate
left join
(select count(distinct app_id) total_business_college_shop,
'${statdate}' statdate
from db_result.r_app_info_dt
where statdate = '${statdate}'
and app_type = 2) t5 on t1.statdate = t5.statdate
left join
(select count(distinct app_id) total_business_college_formal_shop,
'${statdate}' statdate
from db_result.r_app_info_dt
where statdate = '${statdate}' and version_type = 171) t6 on t1.statdate = t6.statdate
left join
(select count(distinct user_id) new_business_college_mandate_user,
'${statdate}' statdate
from db_source.s_db_wework_t_employees_dt
where statdate = '${statdate}'
and is_deleted = 0
and created_at like '${stat_date}%') t7 on t1.statdate = t7.statdate
left join
(select count(distinct f.user_id) active_business_college_shop_user,
'${statdate}' statdate
from 
(
    select app_id
from db_result.r_app_info_dt
where statdate = '${statdate}'
and app_type = 2 
) e
left join
(
    select app_id, user_id
    from db_middle.m_c_report_log_d
    where statdate = '${statdate}'
    and visited_at like '${stat_date}'
) f
on e.app_id = f.app_id
where f.app_id is not null) t8 on t1.statdate = t8.statdate
left join
(select count(distinct h.user_id) total_business_college_mandate_user,
'${statdate}' statdate
from
(
     select app_id
from db_result.r_app_info_dt
where statdate = '${statdate}'
and app_type = 2
) g
left join
(
    select app_id, user_id
from db_source.s_db_wework_t_employees_dt
where statdate = '${statdate}'
and is_deleted = 0
) h
on g.app_id = h.app_id
where h.app_id is not null) t9 on t1.statdate = t9.statdate