二级团队业绩分析报表：销机数、消费、报名数、业绩、费比等指标

WITH dsc AS ( WITH inp AS ( SELECT to_char(to_date(:Year || '-' || :Month, 'YYYY-mm-dd'), 'YYYY-MM') ym ) SELECT ds.first_allotted_group 二级团队, count(ds.chance_openid) c_co, sum( CASE WHEN ds.city_name IN ( '广东省', '深圳市', '广州市', '佛山市', '东莞市', '汕头市', '珠海市' ) AND ds.推广渠道名称 ~* '百度|抖音|华师|小红书|快手|SEO|360|朋友圈|维新' THEN 140 WHEN ds.city_name IN ( '广东省', '深圳市', '广州市', '佛山市', '东莞市', '汕头市', '珠海市' ) AND ds.推广渠道名称 ~* '好学校|美团|学历提升APP|学历咨询|品牌|公众号' THEN 0 WHEN ds.推广渠道名称 LIKE '%学历咨询%' THEN 0 WHEN ds.推广渠道名称 LIKE '%中国教育在线%' THEN 150 WHEN ds.city_name = '宁夏' THEN 90 WHEN ds.city_name = '甘肃' THEN 95 WHEN ds.city_name IN ('西安市', '陕西') AND ds.推广渠道名称 ~* '百度|抖音|360|朋友圈|快手' THEN 115 WHEN ds.city_name IN ('西安市', '陕西') AND ds.推广渠道名称 = '维新' THEN 95 WHEN ds.city_name IN ('西安市', '陕西') AND ds.推广渠道名称 = '小鹿' THEN 100 ELSE 150 END ) allpay, count(DISTINCT ds.first_allotted_owner) c_owner FROM dw_sales_chance_infodateat ds CROSS JOIN inp WHERE ds.first_pop_owner IS NOT NULL AND ds.first_allotted_group IS NOT NULL AND to_char(ds.first_allotted_at, 'yyyy-mm') = ym GROUP BY ds.first_allotted_group ) , ua AS ( WITH al AS ( WITH inp AS ( SELECT to_char(to_date(:Year || '-' || :Month, 'YYYY-mm-dd'), 'YYYY-MM') ym ) SELECT ds.first_allotted_group, CASE WHEN to_char(ua.created_at, 'yyyy-mm') = to_char(ds.pop_created_at, 'yyyy-mm') AND pop_owner IS NOT NULL THEN '当期' WHEN to_char(ua.created_at, 'yyyy-mm') <> to_char(ds.pop_created_at, 'yyyy-mm') AND pop_owner IS NOT NULL THEN '往期' ELSE '自营' END 类型, ua.student_name, ua.created_at, ds.created_at, ua.sn, 推广渠道名称, CASE WHEN ds.city_name IN ( '广东省', '深圳市', '广州市', '佛山市', '东莞市', '汕头市', '珠海市' ) AND ds.推广渠道名称 ~* '好学校|美团|学历提升APP|学历咨询|品牌|公众号' OR 推广渠道名称 LIKE '%学历咨询%' THEN 报名金额 * 0.2 ELSE 0 END per_pay, ua.报名金额, tc.结算类型, CASE WHEN ua.product_package_name LIKE '%成考%' AND ua.product_package_name LIKE '%特供冲刺直本班%' THEN 1 ELSE 0 END 特供冲刺直本班个数, CASE WHEN 结算类型 = '半结算类-开放套读大自考' THEN 1 WHEN 结算类型 = '半结算类-开放套读小自考' THEN 1 WHEN 结算类型 = '结算类-开放' THEN 1 ELSE 0 END 开放个数, CASE WHEN 结算类型 = '半结算类-开放套读小自考' THEN 1 WHEN 结算类型 = '结算类-小自考' THEN 1 WHEN 结算类型 = '半结算类-小自考套读大自考' THEN 1 WHEN 结算类型 = '结算类-小自考套读' THEN 2 ELSE 0 END 小自考个数, CASE WHEN aging_name ~ '好学付|先享后付|爱学分|内部分期' THEN 0 END 账单分期, ds.chance_openid, ds.pop_created_at, ds.first_pop_owner, ds.pop_owner FROM user_apply_detail ua LEFT JOIN dw_sales_chance_infodateat ds ON ds.chance_openid = ua.chance_openid LEFT JOIN 套餐分类 tc ON ua.product_package_name = tc.套餐名 CROSS JOIN inp WHERE to_char(ua.created_at, 'yyyy-mm') = ym ) SELECT first_allotted_group 二级团队, sum(per_pay) per_pay, sum( CASE WHEN 类型 = '当期' THEN 1 ELSE 0 END ) ct_dq, sum( CASE WHEN 类型 = '往期' THEN 1 ELSE 0 END ) ct_hf, sum( CASE WHEN 类型 = '自营' THEN 1 ELSE 0 END ) ct_zy, count(sn) c_sn, sum(特供冲刺直本班个数) 特供冲刺直本班数量, sum(开放个数) 开放数量, sum(小自考个数) 小自考数量, sum( CASE WHEN 类型 = '当期' THEN 报名金额 ELSE 0 END ) sale_dq, sum( CASE WHEN 类型 = '往期' THEN 报名金额 ELSE 0 END ) sale_hf, sum( CASE WHEN 类型 = '自营' THEN 报名金额 ELSE 0 END ) sale_zy, sum(报名金额) sale_mon, sum( 报名金额 - coalesce(账单分期, 特供冲刺直本班个数 * 3000) - coalesce(账单分期, 开放个数 * 4500) - coalesce(账单分期, 小自考个数 * 3000) ) 净额流水 FROM al GROUP BY 二级团队 ) SELECT coalesce(dsc.二级团队, ua.二级团队) 二级团队, c_co 销机数, allpay + coalesce(per_pay, 0) 消费, coalesce(ct_dq, 0) 当期报名, coalesce(sale_dq, 0) 当期业绩, coalesce(ct_hf, 0) 回访报名, coalesce(sale_hf, 0) 回访业绩, coalesce(ct_dq + ct_hf, 0) 流量当月报名数, coalesce(sale_dq + sale_hf, 0) 流量当月报名金额, coalesce(ct_zy, 0) 自营报名, coalesce(sale_zy, 0) 自营业绩, coalesce(c_sn, 0) 月综合报名, coalesce(sale_mon, 0) 月综合业绩, dsc.c_owner 咨询人数 FROM dsc LEFT JOIN ua ON dsc.二级团队 = ua.二级团队 ) a SELECT 二级团队, 销机数, 消费, 当期报名, 当期业绩, CASE WHEN 销机数 <> 0 THEN 当期报名 / 销机数::numeric ELSE 0 END::numeric(7, 6) 当期销转, CASE WHEN 当期业绩 <> 0 THEN 消费 / 当期业绩 ELSE 0 END::numeric(7, 6) 当期费比, 回访报名, 回访业绩, CASE WHEN 销机数 <> 0 THEN 流量当月报名数 / 销机数::numeric ELSE 0 END::numeric(7, 6) 总流量销转, CASE WHEN 流量当月报名金额 <> 0 THEN 消费 / 流量当月报名金额 ELSE 0 END::numeric(7, 6) 总流量费比, 自营报名, 自营业绩, 月综合报名, 月综合业绩, CASE WHEN 月综合业绩 <> 0 THEN 消费 / 月综合业绩 ELSE 0 END::numeric(7, 6) 月综合费比, CASE WHEN 流量当月报名数 <> 0 THEN 消费 / 流量当月报名数 ELSE 0 END::numeric(10, 2) 报名成本（流量）, CASE WHEN 月综合报名 <> 0 THEN 月综合业绩 / 月综合报名 ELSE 0 END::numeric(10, 2) ARPU, 咨询人数, CASE WHEN 咨询人数 <> 0 THEN 月综合业绩 / 咨询人数 ELSE 0 END::numeric(10, 2) 人效（流量） FROM a ORDER BY 销机数 DES