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

WITH a AS(
    SELECT team_id, team_name, plan_level
    FROM teams
    WHERE plan_level = 'Enterprise'
),
b AS(
    SELECT team_id, feature_name, usage_timestamp
    FROM feature_usage
    WHERE feature_name  =  'Advanced_Analytics'  
        AND usage_timestamp >= '2025-04-01' AND usage_timestamp < '2025-05-01'
),
c AS(
    SELECT b.team_id, team_name, COUNT(usage_timestamp) april_usage_count,
        CASE WHEN COUNT(usage_timestamp) > 50 THEN '深度采纳团队' ELSE '普通采纳团队' END adoption_category
    FROM a 
    JOIN b USING(team_id)
    GROUP BY 1,2 
),
d AS(
    SELECT team_id, DATE(MIN(usage_timestamp)) first_ever_usage_date
    FROM feature_usage
    GROUP BY 1
)
SELECT c.team_id, team_name, april_usage_count, adoption_category, first_ever_usage_date
FROM c 
JOIN d USING(team_id)
ORDER BY  FIELD(adoption_category,'深度采纳团队','普通采纳团队'), april_usage_count DESC, 1