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

with t1 as(
    select DISTINCT teams.team_id,team_name
    from teams join feature_usage using(team_id)
    where feature_name = 'Advanced_Analytics' and plan_level = 'Enterprise'
    and date(usage_timestamp) between '2025-04-01' and '2025-04-30'
),### 记录4月有使用了 'Advanced_Analytics' 功能的 'Enterprise' 套餐团队，去重
min_t as(
    select t1.team_id,team_name,
    date_format(min(date(usage_timestamp)),'%Y-%m-%d') first_ever_usage_date
    from feature_usage join t1 using(team_id)
    group by team_id,team_name
) ### 计算筛选出的队伍首时间

select min_t.team_id,team_name,
count(*) april_usage_count,
case when count(*) > 50 then '深度采纳团队' else '普通采纳团队' end adoption_category,
first_ever_usage_date
from feature_usage right join min_t using(team_id)
where date(usage_timestamp) between '2025-04-01' and '2025-04-30'
and feature_name = 'Advanced_Analytics'
group by team_id,team_name,first_ever_usage_date
order by adoption_category desc,april_usage_count desc,team_id