

-- 签约偏好分析
select p1.*
       ,p2.* 
  from
       ( -- 员工
        select ali_code
               ,employee_name
               ,l3_dept_name
               ,l4_dept_name
               ,hire_months
               ,city_name
               ,role_name
               ,role_type
          from kbreport.dim_gd_c33_employee_info_ds
         where ds = '20260531'
           and l2_dept_name in ('医疗行业线','教培行业线')
           and is_leave = 0 
       ) p1 
  left join 
       (
        select ds
               ,substring(ds,1,6) as smonth 
               ,ali_code
               ,shop_id
               ,shop_name
               ,partner_id
               ,partner_name
               ,l2_srvline
               ,l3_srvline
               ,l4_srvline
               ,sum(new_sign_shop_cnt)      as new_sign_shop_cnt
               ,sum(new_sign_income_amt) as new_sign_income_amt
          from kbreport.dwd_gd_sign_employee_income_detail_ds
         where ds <= '20260531'
           and ds >= '20260101'
           and new_sign_shop_cnt > 0
         group by ds
               ,substring(ds,1,6) 
               ,ali_code
               ,shop_id
               ,shop_name
               ,partner_id
               ,partner_name
               ,l2_srvline
               ,l3_srvline
               ,l4_srvline  
       ) p2
    on p1.ali_code = p2.ali_code



-- 签约类目分布

select p1.smonth
       ,p1.ali_code
       ,p1.hire_months
       ,p1.l2_dept_name
       ,p1.l3_dept_name
       ,p1.employee_name
       ,coalesce(count(distinct p2.shop_id)   )  as 门店数
       ,coalesce(count(distinct p2.partner_id))  as 商户数
       ,sum(coalesce(p2.new_sign_income_amt,0))  as 年费收入
  from
       (
        select ali_code
               ,ds  
               ,substring(ds,1,6) as smonth
               ,employee_name
               ,hire_months 
               ,l2_dept_name
               ,l3_dept_name
          from kbreport.dim_gd_c33_employee_info_ds   
         where ds <= '20260531'
           and ds >= '20260101'
           and is_leave = 0
           and l2_dept_name in ('医疗行业线','教培行业线')      
           and role_type <> '管理' 
           and l3_dept_name <> '商机拓展组'
       ) p1  
  left join 
       (
        select ds
               ,substring(ds,1,6) as smonth 
               ,ali_code
               ,shop_id
               ,shop_name
               ,partner_id
               ,partner_name
               ,l2_srvline
               ,l3_srvline
               ,l4_srvline
               ,sum(new_sign_shop_cnt)      as new_sign_shop_cnt
               ,sum(new_sign_income_amt)    as new_sign_income_amt
          from kbreport.dwd_gd_sign_employee_income_detail_ds
         where ds <= '20260531'
           and ds >= '20260101'
           and (new_sign_shop_cnt > 0 or new_sign_income_amt > 0)
         group by ds
               ,substring(ds,1,6)
               ,ali_code
               ,shop_id
               ,shop_name
               ,partner_id
               ,partner_name
               ,l2_srvline
               ,l3_srvline
               ,l4_srvline  
       ) p2 
    on p1.ali_code = p2.ali_code
   and p1.ds = p2.ds  
 group by p1.smonth
       ,p1.ali_code
       ,p1.hire_months
       ,p1.l2_dept_name
       ,p1.l3_dept_name
       ,p1.employee_name