WITH first_cohort AS (
  SELECT DISTINCT
    CASE WHEN 合作方名 IS NULL OR 合作方名='' THEN 'null' ELSE 合作方名 END AS partner_name,
    CASE WHEN 公众号名 IS NULL OR 公众号名='' THEN 'null' ELSE 公众号名 END AS account_name,
    mid AS first_mid,
    subsessionid AS rootsessionid
  FROM loghubods.opengid_base_data
  WHERE dt='20260712'
    AND channel='公众号合作-即转-稳定'
    AND usersharedepth=0
    AND hotsencetype != 1167
    AND videoid IS NOT NULL
    AND mid IS NOT NULL
    AND subsessionid IS NOT NULL
),
base_stats AS (
  SELECT partner_name, account_name,
         COUNT(DISTINCT first_mid) AS first_uv,
         COUNT(DISTINCT rootsessionid) AS root_session_cnt
  FROM first_cohort
  GROUP BY partner_name, account_name
  HAVING COUNT(DISTINCT first_mid)>500
),
root_entity AS (
  SELECT DISTINCT f.partner_name, f.account_name, f.rootsessionid
  FROM first_cohort f
  JOIN base_stats b ON f.partner_name=b.partner_name AND f.account_name=b.account_name
),
first_users AS (
  SELECT DISTINCT f.partner_name, f.account_name, f.first_mid
  FROM first_cohort f
  JOIN base_stats b ON f.partner_name=b.partner_name AND f.account_name=b.account_name
),
raw_active_fission AS (
  SELECT DISTINCT r.partner_name, r.account_name, s.machinecode AS active_mid, s.dt AS active_dt
  FROM root_entity r
  JOIN loghubods.user_share_log s
    ON s.rootsessionid=r.rootsessionid
  WHERE s.dt BETWEEN '20260712' AND '20260727'
    AND s.topic='click'
    AND s.machinecode IS NOT NULL
    AND CAST(s.usersharedepth AS BIGINT)>0
),
active_fission AS (
  SELECT c.*
  FROM raw_active_fission c
  LEFT JOIN first_users f
    ON c.partner_name=f.partner_name AND c.account_name=f.account_name AND c.active_mid=f.first_mid
  WHERE f.first_mid IS NULL
),
active_counts AS (
  SELECT partner_name, account_name,
    SUM(CASE WHEN active_dt='20260712' THEN 1 ELSE 0 END) AS a0_active_uv,
    SUM(CASE WHEN active_dt='20260713' THEN 1 ELSE 0 END) AS a1_active_uv,
    SUM(CASE WHEN active_dt='20260714' THEN 1 ELSE 0 END) AS a2_active_uv,
    SUM(CASE WHEN active_dt='20260715' THEN 1 ELSE 0 END) AS a3_active_uv,
    SUM(CASE WHEN active_dt='20260716' THEN 1 ELSE 0 END) AS a4_active_uv,
    SUM(CASE WHEN active_dt='20260717' THEN 1 ELSE 0 END) AS a5_active_uv,
    SUM(CASE WHEN active_dt='20260718' THEN 1 ELSE 0 END) AS a6_active_uv,
    SUM(CASE WHEN active_dt='20260719' THEN 1 ELSE 0 END) AS a7_active_uv,
    SUM(CASE WHEN active_dt='20260720' THEN 1 ELSE 0 END) AS a8_active_uv,
    SUM(CASE WHEN active_dt='20260721' THEN 1 ELSE 0 END) AS a9_active_uv,
    SUM(CASE WHEN active_dt='20260722' THEN 1 ELSE 0 END) AS a10_active_uv,
    SUM(CASE WHEN active_dt='20260723' THEN 1 ELSE 0 END) AS a11_active_uv,
    SUM(CASE WHEN active_dt='20260724' THEN 1 ELSE 0 END) AS a12_active_uv,
    SUM(CASE WHEN active_dt='20260725' THEN 1 ELSE 0 END) AS a13_active_uv,
    SUM(CASE WHEN active_dt='20260726' THEN 1 ELSE 0 END) AS a14_active_uv,
    SUM(CASE WHEN active_dt='20260727' THEN 1 ELSE 0 END) AS a15_active_uv,
    COUNT(*) AS active_person_days
  FROM active_fission
  GROUP BY partner_name, account_name
)
SELECT
  '20260712' AS cohort_dt,
  '公众号合作-即转-稳定' AS channel,
  b.partner_name,
  b.account_name,
  b.first_uv,
  b.root_session_cnt,
  NVL(a.a0_active_uv,0) AS a0_active_uv,
  NVL(a.a1_active_uv,0) AS a1_active_uv,
  NVL(a.a2_active_uv,0) AS a2_active_uv,
  NVL(a.a3_active_uv,0) AS a3_active_uv,
  NVL(a.a4_active_uv,0) AS a4_active_uv,
  NVL(a.a5_active_uv,0) AS a5_active_uv,
  NVL(a.a6_active_uv,0) AS a6_active_uv,
  NVL(a.a7_active_uv,0) AS a7_active_uv,
  NVL(a.a8_active_uv,0) AS a8_active_uv,
  NVL(a.a9_active_uv,0) AS a9_active_uv,
  NVL(a.a10_active_uv,0) AS a10_active_uv,
  NVL(a.a11_active_uv,0) AS a11_active_uv,
  NVL(a.a12_active_uv,0) AS a12_active_uv,
  NVL(a.a13_active_uv,0) AS a13_active_uv,
  NVL(a.a14_active_uv,0) AS a14_active_uv,
  NVL(a.a15_active_uv,0) AS a15_active_uv,
  CASE WHEN b.first_uv>0 THEN NVL(a.a0_active_uv,0)/b.first_uv END AS a0_active_rate,
  CASE WHEN b.first_uv>0 THEN NVL(a.a1_active_uv,0)/b.first_uv END AS a1_active_rate,
  CASE WHEN b.first_uv>0 THEN NVL(a.a2_active_uv,0)/b.first_uv END AS a2_active_rate,
  CASE WHEN b.first_uv>0 THEN NVL(a.a3_active_uv,0)/b.first_uv END AS a3_active_rate,
  CASE WHEN b.first_uv>0 THEN NVL(a.a4_active_uv,0)/b.first_uv END AS a4_active_rate,
  CASE WHEN b.first_uv>0 THEN NVL(a.a5_active_uv,0)/b.first_uv END AS a5_active_rate,
  CASE WHEN b.first_uv>0 THEN NVL(a.a6_active_uv,0)/b.first_uv END AS a6_active_rate,
  CASE WHEN b.first_uv>0 THEN NVL(a.a7_active_uv,0)/b.first_uv END AS a7_active_rate,
  CASE WHEN b.first_uv>0 THEN NVL(a.a8_active_uv,0)/b.first_uv END AS a8_active_rate,
  CASE WHEN b.first_uv>0 THEN NVL(a.a9_active_uv,0)/b.first_uv END AS a9_active_rate,
  CASE WHEN b.first_uv>0 THEN NVL(a.a10_active_uv,0)/b.first_uv END AS a10_active_rate,
  CASE WHEN b.first_uv>0 THEN NVL(a.a11_active_uv,0)/b.first_uv END AS a11_active_rate,
  CASE WHEN b.first_uv>0 THEN NVL(a.a12_active_uv,0)/b.first_uv END AS a12_active_rate,
  CASE WHEN b.first_uv>0 THEN NVL(a.a13_active_uv,0)/b.first_uv END AS a13_active_rate,
  CASE WHEN b.first_uv>0 THEN NVL(a.a14_active_uv,0)/b.first_uv END AS a14_active_rate,
  CASE WHEN b.first_uv>0 THEN NVL(a.a15_active_uv,0)/b.first_uv END AS a15_active_rate,
  NVL(a.active_person_days,0) AS active_person_days,
  CASE WHEN b.first_uv>0 THEN (NVL(a.a0_active_uv,0)+NVL(a.a1_active_uv,0)+NVL(a.a2_active_uv,0)+NVL(a.a3_active_uv,0)+NVL(a.a4_active_uv,0)+NVL(a.a5_active_uv,0)+NVL(a.a6_active_uv,0)+NVL(a.a7_active_uv,0)+NVL(a.a8_active_uv,0)+NVL(a.a9_active_uv,0)+NVL(a.a10_active_uv,0)+NVL(a.a11_active_uv,0)+NVL(a.a12_active_uv,0)+NVL(a.a13_active_uv,0)+NVL(a.a14_active_uv,0)+NVL(a.a15_active_uv,0))/b.first_uv END AS activity_rate_sum,
  CASE WHEN NVL(a.a0_active_uv,0)>0
       THEN ((NVL(a.a0_active_uv,0)+NVL(a.a1_active_uv,0)+NVL(a.a2_active_uv,0)+NVL(a.a3_active_uv,0)+NVL(a.a4_active_uv,0)+NVL(a.a5_active_uv,0)+NVL(a.a6_active_uv,0)+NVL(a.a7_active_uv,0)+NVL(a.a8_active_uv,0)+NVL(a.a9_active_uv,0)+NVL(a.a10_active_uv,0)+NVL(a.a11_active_uv,0)+NVL(a.a12_active_uv,0)+NVL(a.a13_active_uv,0)+NVL(a.a14_active_uv,0)+NVL(a.a15_active_uv,0))-NVL(a.a0_active_uv,0))/a.a0_active_uv
  END AS active_tail_vs_a0,
  CASE WHEN NVL(a.a0_active_uv,0)>0
       THEN (NVL(a.a0_active_uv,0)+NVL(a.a1_active_uv,0)+NVL(a.a2_active_uv,0)+NVL(a.a3_active_uv,0)+NVL(a.a4_active_uv,0)+NVL(a.a5_active_uv,0)+NVL(a.a6_active_uv,0)+NVL(a.a7_active_uv,0)+NVL(a.a8_active_uv,0)+NVL(a.a9_active_uv,0)+NVL(a.a10_active_uv,0)+NVL(a.a11_active_uv,0)+NVL(a.a12_active_uv,0)+NVL(a.a13_active_uv,0)+NVL(a.a14_active_uv,0)+NVL(a.a15_active_uv,0))/a.a0_active_uv
  END AS activity_maturity_vs_a0,
  CASE WHEN b.first_uv>0 THEN 1+(NVL(a.a0_active_uv,0)+NVL(a.a1_active_uv,0)+NVL(a.a2_active_uv,0)+NVL(a.a3_active_uv,0)+NVL(a.a4_active_uv,0)+NVL(a.a5_active_uv,0)+NVL(a.a6_active_uv,0)+NVL(a.a7_active_uv,0)+NVL(a.a8_active_uv,0)+NVL(a.a9_active_uv,0)+NVL(a.a10_active_uv,0)+NVL(a.a11_active_uv,0)+NVL(a.a12_active_uv,0)+NVL(a.a13_active_uv,0)+NVL(a.a14_active_uv,0)+NVL(a.a15_active_uv,0))/b.first_uv END AS steady_dau_multiplier
FROM base_stats b
LEFT JOIN active_counts a ON b.partner_name=a.partner_name AND b.account_name=a.account_name
ORDER BY b.first_uv DESC
