题解 | SaaS平台企业客户新功能采纳度分析

WITH t AS (
    SELECT t1.team_id,team_name,
           COUNT(usage_id) AS april_usage_count,
           CASE WHEN COUNT(usage_id)>50 THEN '深度采纳团队' ELSE '普通采纳团队' END AS adoption_category
    FROM teams t1
    LEFT JOIN feature_usage f ON t1.team_id=f.team_id
    WHERE substr(usage_timestamp,1,7)='2025-04' AND plan_level='Enterprise' AND feature_name='Advanced_Analytics'
    GROUP BY t1.team_id,team_name

),
t2 AS (
     SELECT team_id,
            DATE_FORMAT(MIN(usage_timestamp),'%Y-%m-%d') AS first_ever_usage_date
     FROM feature_usage
     GROUP BY team_id
)
SELECT t.*,t2.first_ever_usage_date
FROM t
LEFT JOIN t2 ON t.team_id=t2.team_id
ORDER BY adoption_category DESC,april_usage_count DESC,t.team_id ASC