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

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