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

WITH first_usage AS (
    SELECT 
        team_id,
        DATE_FORMAT(MIN(usage_timestamp), '%Y-%m-%d') AS first_ever_usage_date
    FROM feature_usage
    GROUP BY team_id
),

total_usage AS (
    SELECT
        team_id,
        COUNT(*) AS total_usage_count
    FROM feature_usage
    GROUP BY team_id
),

april_usage AS (
    SELECT 
        t1.team_id,
        t2.team_name,
        COUNT(*) AS april_usage_count
    FROM feature_usage t1
    LEFT JOIN teams t2 USING (team_id)
    WHERE t1.usage_timestamp >= '2025-04-01'
      AND t1.usage_timestamp <  '2025-05-01'
      AND t1.feature_name = 'Advanced_Analytics'
      AND t2.plan_level = 'Enterprise'
    GROUP BY 
        t1.team_id,
        t2.team_name
)

SELECT 
    a.team_id,
    a.team_name,
    a.april_usage_count,
    CASE 
        WHEN t.total_usage_count > 50 THEN '深度采纳团队'
        ELSE '普通采纳团队'
    END AS adoption_category,
    f.first_ever_usage_date
FROM april_usage a
LEFT JOIN first_usage f ON a.team_id = f.team_id
LEFT JOIN total_usage t ON a.team_id = t.team_id

ORDER BY 
    adoption_category DESC,
    april_usage_count DESC,
    a.team_id ASC;