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

with data as (
    select team_id,team_name,count(*) april_usage_count,
    if(count(*)>50,'深度采纳团队','普通采纳团队') adoption_category
    from (
        select fu.team_id,team_name,plan_level,creation_date,usage_id,feature_name,usage_timestamp
        from feature_usage fu left join teams t on fu.team_id = t.team_id
        where usage_timestamp like '2025-04%' and feature_name = 'Advanced_Analytics' and plan_level = 'Enterprise'
    ) t1 
    group by team_id,team_name
),
rk_data as (
    select team_id,usage_timestamp first_ever_usage_date from (
        select fu.team_id,date(usage_timestamp) usage_timestamp,
        rank()over(partition by fu.team_id order by usage_timestamp) rk
        from feature_usage fu left join teams t on fu.team_id = t.team_id
    ) t2 
    where rk=1
)
select distinct data.team_id,team_name,april_usage_count,adoption_category,first_ever_usage_date
from data left join rk_data on data.team_id = rk_data.team_id
order by adoption_category desc,april_usage_count desc,team_id