题解 | 每个创作者每月的涨粉率及截止当前的总粉丝量
每个创作者每月的涨粉率及截止当前的总粉丝量
https://www.nowcoder.com/practice/d337c95650f640cca29c85201aecff84
SELECT
author,
month,
fans_growth_rate,
SUM(fans_cnt) OVER(PARTITION BY author ORDER BY month) AS total_fans # SUM窗口函数
FROM(
SELECT # 先把fans_growth_rate计算出来，这里我先把每个月的新增粉丝量给计算出来了
author,
DATE_FORMAT(end_time, '%Y-%m') AS month,
ROUND(SUM(IF(if_follow = 2, -1, if_follow)) / COUNT(if_follow), 3) AS fans_growth_rate,
SUM(IF(if_follow = 2, -1, if_follow)) AS fans_cnt # 每月新增粉丝
FROM tb_user_video_log tu
JOIN tb_video_info tv
ON tu.video_id = tv.video_id
WHERE YEAR(start_time) = 2021 #条件计算2021年
GROUP BY author, month
) AS tb1
ORDER BY author, total_fans #隐藏条件
查看15道真题和解析
小天才公司福利 1313人发布