--有点神经的做法了
with tiaojian as (
select 
team_id, 
team_name
from teams
where
plan_level='Enterprise'
),tiaojian1 as (
select 
t.team_id, 
t.team_name,
count(t.team_id) as april_usage_count,
case when count(t.team_id)>50 then '深度采纳团队' else '普通采纳团队' end as adoption_category
from tiaojian t inner join feature_usage fg on t.team_id=fg.team_id
where
date_format(usage_timestamp,'%Y%m')=202504
group by t.team_id,t.team_name
),
tiaojian2 as (
select 
t.team_id,
date(usage_timestamp) as first_ever_usage_date
from(
select 
t.team_id,
usage_timestamp,
dense_rank()over(partition by t.team_id order by usage_timestamp) as py
from tiaojian t inner join feature_usage fg on t.team_id=fg.team_id
) as  t 
where
t.py=1
)


select 
distinct 
t1.team_id,
t1.team_name,
t1.april_usage_count,
t1.adoption_category,
t2.first_ever_usage_date
from tiaojian1 t1 inner join tiaojian2 t2 
on t1.team_id=t2.team_id
order by adoption_category desc,april_usage_count desc,t1.team_id