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

with temp0 as (
    select team_id,min(date_format(usage_timestamp,"%Y-%m-%d")) as first_ever_usage_date
    from feature_usage
    group by team_id
), temp1 as (
    select t.team_id,count(*) as april_usage_count,
    case 
        when count(*) > 50 then "深度采纳团队"
        else "普通采纳团队"
    end as adoption_category
    from teams t inner join feature_usage fu
    on t.team_id = fu.team_id
    where date_format(usage_timestamp,"%Y-%m-%d") >= "2025-04-01" 
    and date_format(usage_timestamp,"%Y-%m-%d") <= "2025-04-30"
    and feature_name = "Advanced_Analytics" and plan_level = "Enterprise"
    group by t.team_id
)

select t0.team_id,team_name,april_usage_count,adoption_category,first_ever_usage_date
from temp0 t0 inner join temp1 t1 on t0.team_id = t1.team_id
inner join teams t on t.team_id = t0.team_id
order by adoption_category desc,april_usage_count desc,t0.team_id asc;