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

WITH temp AS (
    SELECT distinct
        t.team_id,
        t.team_name
    FROM teams t
    INNER JOIN feature_usage fu ON fu.team_id = t.team_id
    WHERE 
        t.plan_level = 'Enterprise'
        AND fu.feature_name = 'Advanced_Analytics'
        AND DATE_FORMAT(fu.usage_timestamp, '%Y-%m') = '2025-04'
), -- 这张表里查找到的是特定的团队姓名，团队id


-- 第二张表查找四月总使用次数
temp1 as (
    select 
    tp.team_id,
    tp.team_name,
    count(fu.usage_id) as april_usage_count
    from temp tp
    inner join feature_usage fu
    on fu.team_id = tp.team_id
    -- 这张表是基于temp表并关联fu表查找的，但是查找聚合的fu.usage_id字段，在temp表中并没有筛选出来，所以需要重新筛选
    where DATE_FORMAT(fu.usage_timestamp, '%Y-%m') = '2025-04'
    and fu.feature_name = 'Advanced_Analytics'
    -- 对没有聚合的字段进行分组
    group by tp.team_id,tp.team_name
),


-- 查找总的使用次数并归类
temp2 as (
    select 
    tp1.team_id,
    tp1.team_name,
    tp1.april_usage_count,
    case
    when april_usage_count > 50 then '深度采纳团队'
    else '普通采纳团队'
    end as adoption_category,
    date_format(min(fu.usage_timestamp),'%Y-%m-%d') as first_ever_usage_date
    from temp1 tp1
    inner join feature_usage fu
    on tp1.team_id = fu.team_id
    GROUP BY tp1.team_id, tp1.team_name, tp1.april_usage_count
)
-- 最终查询：按需求排序输出所有字段
SELECT * 
FROM temp2
ORDER BY 
    adoption_category DESC,  -- 深度采纳在前
    april_usage_count DESC,  -- 使用次数降序
    team_id ASC;  -- 团队ID升序