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

WITH use_fre AS (

SELECT

t.team_id,

t.team_name,

COUNT(f.usage_timestamp) AS april_usage_count,

CASE WHEN COUNT(f.usage_timestamp) > 50 THEN '深度采纳团队' ELSE '普通采纳团队' END AS adoption_category

FROM teams t

JOIN feature_usage f ON t.team_id = f.team_id

WHERE f.usage_timestamp >= '2025-04-01'

AND f.usage_timestamp < '2025-05-01'

AND f.feature_name = 'Advanced_Analytics'

AND t.plan_level = 'Enterprise'

GROUP BY

t.team_id,

t.team_name

),

use_date AS (

SELECT

t.team_id,

DATE_FORMAT(MIN(f.usage_timestamp), '%Y-%m-%d') AS first_ever_usage_date

FROM teams t

JOIN feature_usage f ON t.team_id = f.team_id

GROUP BY

t.team_id

)

SELECT

t1.team_id,

t1.team_name,

t1.april_usage_count,

t1.adoption_category,

t2.first_ever_usage_date

FROM use_fre t1

JOIN use_date t2 ON t1.team_id = t2.team_id

ORDER BY

CASE WHEN adoption_category = '深度采纳团队' THEN 1 ELSE 0 END DESC,

april_usage_count DESC,

team_id;