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

 # 1、筛选特定团队，并计算使用功能次数
with
    t1 as (
        select
            fu.team_id,
            t.team_name,
            sum(
                case
                    when feature_name = 'Advanced_Analytics' then 1
                    else 0
                end
            ) as april_usage_count
        from
            feature_usage fu
            left join teams t on fu.team_id = t.team_id
        where
            fu.usage_timestamp between '2025-04-01' and '2025-04-30'
            and fu.feature_name = 'Advanced_Analytics'
            and t.plan_level = 'Enterprise'
        group by
            fu.team_id,
            t.team_name
    ),
    # 2、计算团队最早使用日期
    t2 as (
        select
            team_id,
            min(date(usage_timestamp)) as first_ever_usage_date
        from
            feature_usage
        group by
            team_id
    )
    # 3、合并
select
    t1.team_id,
    team_name,
    april_usage_count,
    case
        when april_usage_count > 50 then '深度采纳团队'
        else '普通采纳团队'
    end as adoption_category,
    first_ever_usage_date
from
    t1
    left join t2 on t1.team_id = t2.team_id
order by
    case
        when adoption_category = '深度采纳团队' then 1
        else 2
    end,
    april_usage_count desc,
    team_id asc;