编辑代码

-- r_app_info_dt 
-- dwd_app_extend_info_dt
-- dwd_app_active_log_d
-- B端

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

-- 新增付费店铺数

select count(distinct app_id) new_pay_knowledge_shop,
'${statdate}' statdate
from db_middle.dwd_app_extend_info_dt
where statdate = '${statdate}'
and app_type = 1
and app_created_at like '${stat_date}%'
and first_pay_version_time like '${stat_date}%';

-- 活跃店铺数
select count(distinct a.app_id) active_knowledge_shop,
'${statdate}' statdate
from
(select app_id
from db_result.r_app_info_dt
where statdate = '${statdate}'
and app_type = 1) 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_pay_Knowledge_shop,
'${statdate}' statdate
from
(
    select app_id
    from db_middle.dwd_app_extend_info_dt
    where statdate = '${statdate}'
    and app_type = 1
    and is_paid_app = 1
) 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_knowledge_shop,
'${statdate}' statdate
from db_result.r_app_info_dt
where statdate = '${statdate}'
and app_type = 1;

-- 累计付费店铺数
select count(distinct app_id) total_pay_knowledge_shop,
'${statdate}' statdate
from db_middle.dwd_app_extend_info_dt
where statdate = '${statdate}'
and is_paid_app = 1
and app_type = 1;

-- C端 m_db_ex_business_t_users_full
-- 新增用户
select count(distinct f.user_id) new_knowledge_shop_user,
'${statdate}' statdate
from
(
    select app_id
    from db_result.r_app_info_dt
    where statdate = '${statdate}'
and app_type = 1
) e
left join
(select app_id, user_id
from db_middle.m_db_ex_business_t_users_full
where statdate = '${statdate}'
and created_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) active_knowledge_shop_user,
'${statdate}' statdate
from
(
    select app_id
    from db_result.r_app_info_dt
    where statdate = '${statdate}'
    and app_type = 1
) g
left join
(
    select app_id, user_id
    from db_middle.dwd_c_page_detail_d
    where statdate = '${statdate}'
    and visited_at like '${stat_date}%'
) h on g.app_id = h.app_id
where h.app_id is not null;

-- 累计用户

select count(distinct k.user_id) total_knowledge_shop_user,
'${statdate}' statdate
from
(
    select app_id
    from db_result.r_app_info_dt
    where statdate = '${statdate}'
    and app_type = 1
) j
left join
(
    select app_id, user_id
    from db_middle.m_db_ex_business_t_users_full
    where statdate = '${statdate}'
) k on j.app_id = k.app_id
where k.app_id is not null;

-- 新增讲师
-- s_db_ex_alive_t_alive_role_dt
-- s_db_ex_alive_t_alive_module_conf_dt
select *
from
(
    select alive_id
    from db_source.s_db_ex_alive_t_alive_role_dt
    where is_current_lecturer = 1
    and created_at like '${stat_date}%'
    and state = 0
)


-- 活跃讲师
-- 累计讲师
select count(distinct user_id)
from db_source.s_db_ex_alive_t_alive_role_dt
where statdate = '${statdate}'
and is_current_lecturer = 1
and state = 0;
-- GMV


-- 知识店铺

select '${stat_date}' count_date,
coalesce(t1.new_knowledge_shop, 0) new_knowledge_shop,
coalesce(t2.new_pay_knowledge_shop, 0) new_pay_knowledge_shop,
coalesce(t3.active_knowledge_shop, 0) active_knowledge_shop,
coalesce(t4.active_pay_Knowledge_shop, 0) active_pay_Knowledge_shop,
coalesce(t5.total_knowledge_shop, 0) total_knowledge_shop,
coalesce(t6.total_pay_knowledge_shop, 0) total_pay_knowledge_shop,
coalesce(t7.new_knowledge_shop_user, 0) new_knowledge_shop_user,
coalesce(t8.active_knowledge_shop_user, 0) active_knowledge_shop_user,
coalesce(t9.total_knowledge_shop_user, 0) total_knowledge_shop_user,
coalesce(null, 0) new_teacher_knowledge_shop,
coalesce(null, 0) active_teacher_knowledge_shop,
coalesce(t10.total_teacher_knowledge_shop, 0) total_teacher_knowledge_shop,
coalesce(null, 0) GMV
from
(
    select count(distinct app_id) new_knowledge_shop,
'${statdate}' statdate
from db_result.r_app_info_dt
where statdate = '${statdate}'
and app_type = 1
and app_created_at like '${stat_date}%'
) t1 
left join
(
    select count(distinct app_id) new_pay_knowledge_shop,
'${statdate}' statdate
from db_middle.dwd_app_extend_info_dt
where statdate = '${statdate}'
and app_type = 1
and app_created_at like '${stat_date}%'
and first_pay_version_time like '${stat_date}%'
) t2 on t1.statdate = t2.statdate
left join
(
    select count(distinct a.app_id) active_knowledge_shop,
'${statdate}' statdate
from
(select app_id
from db_result.r_app_info_dt
where statdate = '${statdate}'
and app_type = 1) 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_pay_Knowledge_shop,
'${statdate}' statdate
from
(
    select app_id
    from db_middle.dwd_app_extend_info_dt
    where statdate = '${statdate}'
    and app_type = 1
    and is_paid_app = 1
) 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_knowledge_shop,
'${statdate}' statdate
from db_result.r_app_info_dt
where statdate = '${statdate}'
and app_type = 1;
) t5 on t1.statdate = t5.statdate
left join
(
    select count(distinct app_id) total_pay_knowledge_shop,
'${statdate}' statdate
from db_middle.dwd_app_extend_info_dt
where statdate = '${statdate}'
and is_paid_app = 1
and app_type = 1
) t6 on t1.statdate = t6.statdate
left join
(
    select count(distinct f.user_id) new_knowledge_shop_user,
'${statdate}' statdate
from
(
    select app_id
    from db_result.r_app_info_dt
    where statdate = '${statdate}'
and app_type = 1
) e
left join
(select app_id, user_id
from db_middle.m_db_ex_business_t_users_full
where statdate = '${statdate}'
and created_at like '${stat_date}%') f
on e.app_id = f.app_id
where f.app_id is not null
) t7 on t1.statdate = t7.statdate
left join
(
    select count(distinct h.user_id) active_knowledge_shop_user,
'${statdate}' statdate
from
(
    select app_id
    from db_result.r_app_info_dt
    where statdate = '${statdate}'
    and app_type = 1
) g
left join
(
    select app_id, user_id
    from db_middle.dwd_c_page_detail_d
    where statdate = '${statdate}'
    and visited_at like '${stat_date}%'
) h on g.app_id = h.app_id
where h.app_id is not null
) t8 on t1.statdate = t8.statdate
left join
(
    select count(distinct k.user_id) total_knowledge_shop_user,
'${statdate}' statdate
from
(
    select app_id
    from db_result.r_app_info_dt
    where statdate = '${statdate}'
    and app_type = 1
) j
left join
(
    select app_id, user_id
    from db_middle.m_db_ex_business_t_users_full
    where statdate = '${statdate}'
) k on j.app_id = k.app_id
where k.app_id is not null
) t9 on t1.statdate = t9.statdate
left join
(
    select count(distinct user_id) total_teacher_knowledge_shop,
    '${statdate}' statdate
from db_source.s_db_ex_alive_t_alive_role_dt
where statdate = '${statdate}'
and is_current_lecturer = 1
and state = 0
) t10 on t1.statdate = t10.statdate