题解 | 统计创作者

with t1 as (
    select post_id,author_id,publish_ts,
    rank()over(partition by author_id order by publish_ts desc) rk
    from post
),
time1 as (
    select post_id,author_id,publish_ts first_time
    from t1 
    where rk = 1 
)
select a.author_id,author_name,
count(distinct p.post_id) posts_30d,
sum(like_cnt) likes_30d,
round((sum(like_cnt)/count(distinct p.post_id)),2) avg_likes_30d
from author a 
left join post p on a.author_id = p.author_id
left join time1 on a.author_id = time1.author_id
where timestampdiff(day,time1.first_time,publish_ts) <= 30
group by a.author_id,author_name
order by likes_30d desc,posts_30d desc,a.author_id