题解 | SaaS平台企业客户新功能采纳度分析
SaaS平台企业客户新功能采纳度分析
https://www.nowcoder.com/practice/7b4b67320dde405c8ffdea850467a92d
WITH use_fre AS (
SELECT
t.team_id,
t.team_name,
COUNT(f.usage_timestamp) AS april_usage_count,
CASE WHEN COUNT(f.usage_timestamp) > 50 THEN '深度采纳团队' ELSE '普通采纳团队' END AS adoption_category
FROM teams t
JOIN feature_usage f ON t.team_id = f.team_id
WHERE f.usage_timestamp >= '2025-04-01'
AND f.usage_timestamp < '2025-05-01'
AND f.feature_name = 'Advanced_Analytics'
AND t.plan_level = 'Enterprise'
GROUP BY
t.team_id,
t.team_name
),
use_date AS (
SELECT
t.team_id,
DATE_FORMAT(MIN(f.usage_timestamp), '%Y-%m-%d') AS first_ever_usage_date
FROM teams t
JOIN feature_usage f ON t.team_id = f.team_id
GROUP BY
t.team_id
)
SELECT
t1.team_id,
t1.team_name,
t1.april_usage_count,
t1.adoption_category,
t2.first_ever_usage_date
FROM use_fre t1
JOIN use_date t2 ON t1.team_id = t2.team_id
ORDER BY
CASE WHEN adoption_category = '深度采纳团队' THEN 1 ELSE 0 END DESC,
april_usage_count DESC,
team_id;