编辑代码

-- s_db_ex_admin_wework_t_auth_corp_dt
-- B端
-- 条件（新增企业数）
-- auth_user_portrait=1
-- and state = 1

-- created_at = 今天
-- count(distinct corp_id) 

select count(distinct corp_id) new_business, '${statdate}' statdate
from db_source.s_db_ex_admin_wework_t_auth_corp_dt
where statdate = '${statdate}'
and auth_user_portrait = 1
and state = 1
and created_at like '${stat_date}%'

-- 条件（新增付费企业数）
-- r_b_order_detail_sell_info_dt
-- s_db_ex_admin_wework_t_auth_corp_dt
-- order_real_price > 0 
-- pay_time = 今天
-- business_type = 5

-- 用app_id关联
-- count(distinct corp_id) 

select count(distinct corp_id) '${statdate}' statdate
from 
(select app_id, corp_id, '${statdate}' statdate
from db_source.s_db_ex_admin_wework_t_auth_corp_dt
where statdate = '${statdate}'
and auth_user_portrait = 1
and state = 1
and created_at like '${stat_date}%') a
left join
(select app_id
from db_result.r_b_order_detail_sell_info_dt
where statdate = '$statdate'
and order_real_price > 0
and pay_time like '${stat_date}%'
and business_type = 5) b
on a.app_id = b.app_id and b.app_id is not null;



-- dwd_app_active_log_d
-- s_db_ex_admin_wework_t_auth_corp_dt

select count(distinct t1.corp_id), '${statdate}' statdate
from
(select corp_id, app_id from db_source.s_db_ex_admin_wework_t_auth_corp_dt 
where statdate = '${statdate}') t1
left join
(select app_id from db_middle.dwd_app_active_log_d
where statdate = '${statdate}') t2
on t1.app_id = t2.app_id where t2.app_id is not null;

-- dwd_app_active_log_d
-- s_db_ex_admin_wework_t_auth_corp_dt
-- r_app_info_dt 

select count(distinct corp_id), '${statdate}' statdate
from 
(select corp_id, app_id from db_source.s_db_ex_admin_wework_t_auth_corp_dt 
where statdate = '${statdate}') t3
left join
(select app_id from db_middle.dwd_app_active_log_d
where statdate = '${statdate}' and created_at = '${stat_date}%') t4
on t3.app_id = t4.app_id
left join
(select app_id from db_result.r_app_info_dt
where is_paid_app = 1) t5 
on t3.app_id = t5.app_id
where t4.app_id is not null
and t5.app_id is not null
group by corp_id;





select count(distinct corp_id) from db_source.s_db_ex_admin_wework_t_auth_corp_dt;

select count(distinct corp_id), '${statdate} statdate'
from
(select app_id, corp_id from db_source.s_db_ex_admin_wework_t_auth_corp_dt where statdate = '$statdate') c
left join
(select app_id from db_result.r_b_order_detail_sell_info_dt
where statdate = '$statdate' and order_real_price > 0) d
on c.app_id = d.app_id where d.app_id is not null;

-- C端
-- s_db_ex_admin_wework_t_external_customer_dt
-- state = 1 
-- user_delete_state = 1(目前没有该字段)
-- created_at= 今天
-- count(distinct external_userid) 
select count(distinct external_userid), '${statdate} statdate'
from db_source.s_db_ex_admin_wework_t_external_customer_dt
where statdate = '${statdate}'
and state = 1
and user_delete_state = 1
and created_at = '${stat_date}%'

select count(distinct external_userid), '${statdate} statdate'
from db_source.s_db_ex_admin_wework_t_external_customer_dt
where statdate = '${statdate}'
and state = 1
and user_delete_state = 1


-- 企微助手

select '${stat_date}' count_date,
coalesce(s1.new_business, 0) new_business,
coalesce(s2.new_pay_business, 0) new_pay_business,
coalesce(s3.active_business, 0) active_business,
coalesce(s4.active_pay_business, 0) active_pay_business,
coalesce(s5.total_business, 0) total_business,
coalesce(s6.total_pay_business, 0) total_pay_business,
coalesce(s7.new_external_user, 0) new_external_user,
coalesce(s8.total_external_user, 0) total_external_user
from
(select count(distinct corp_id) new_business, '${statdate}' statdate
from db_source.s_db_ex_admin_wework_t_auth_corp_dt
where statdate = '${statdate}'
and auth_user_portrait = 1
and state = 1
and created_at like '${stat_date}%') s1
left join
(SELECT count(DISTINCT corp_id) new_pay_business,
       '${statdate}' statdate
FROM
  (SELECT app_id,
          corp_id,
          '${statdate}' statdate
   FROM db_source.s_db_ex_admin_wework_t_auth_corp_dt
   WHERE statdate = '${statdate}'
     AND auth_user_portrait = 1
     AND STATE = 1
     AND created_at LIKE '${stat_date}%') a
LEFT JOIN
  (SELECT app_id
   FROM db_result.r_b_order_detail_sell_info_dt
   WHERE statdate = '${statdate}'
     AND order_real_price > 0
     AND pay_time LIKE '${stat_date}%'
     AND business_type = 5) b ON a.app_id = b.app_id
AND b.app_id is not null) s2 on s1.statdate = s2.statdate
left join
(select count(distinct t1.corp_id) active_business, '${statdate}' statdate
from
(select corp_id, app_id from db_source.s_db_ex_admin_wework_t_auth_corp_dt 
where statdate = '${statdate}') t1
left join
(select app_id from db_middle.dwd_app_active_log_d
where statdate = '${statdate}' and created_at like '${stat_date}%') t2
on t1.app_id = t2.app_id where t2.app_id is not null) s3 on s1.statdate = s3.statdate
left join
(SELECT count(DISTINCT t3.corp_id) active_pay_business,
       '${statdate}' statdate
FROM
  (SELECT corp_id,
          app_id
   FROM db_source.s_db_ex_admin_wework_t_auth_corp_dt
   WHERE statdate = '${statdate}') t3
LEFT JOIN
  (SELECT DISTINCT app_id
   FROM db_middle.dwd_app_active_log_d
   WHERE statdate = '${statdate}'
     AND created_at LIKE '${stat_date}%') t4 ON t3.app_id = t4.app_id
LEFT JOIN
  (SELECT app_id
   FROM db_result.r_app_info_dt
   WHERE statdate = '${statdate}'
     AND is_paid_app = 1) t5 ON t3.app_id = t5.app_id
WHERE t4.app_id IS NOT NULL
  AND t5.app_id IS NOT NULL) s4 on s1.statdate = s4.statdate
left join
(select count(distinct corp_id) total_business, '${statdate}' statdate
from db_source.s_db_ex_admin_wework_t_auth_corp_dt where statdate = '${statdate}') s5 on s1.statdate = s5.statdate
left join
(select count(distinct corp_id) total_pay_business, '${statdate}' statdate
from
(select app_id, corp_id from db_source.s_db_ex_admin_wework_t_auth_corp_dt where statdate = '${statdate}') c
left join
(select app_id from db_result.r_b_order_detail_sell_info_dt
where statdate = '${statdate}' and order_real_price > 0) d
on c.app_id = d.app_id where d.app_id is not null) s6 on s1.statdate = s6.statdate
left join
(select count(distinct external_userid) new_external_user, '${statdate}' statdate
from db_source.s_db_ex_admin_wework_t_external_customer_dt
where statdate = '${statdate}'
and state = 1
and created_at = '${stat_date}%') s7 on s1.statdate = s7.statdate
left join
(select count(distinct external_userid) total_external_user, '${statdate}' statdate
from db_source.s_db_ex_admin_wework_t_external_customer_dt
where statdate = '${statdate}'
and state = 1) s8 on s1.statdate = s8.statdate