题解 | 统计创作者

select
*
from (
    select
post.author_id 
,author_name 
,count(1) posts_30d 
,sum(like_cnt) likes_30d 
,ifnull(ROUND(sum(like_cnt) / count(1), 2),0) avg_likes_30d
from author join post on post.author_id = author.author_id
where date_format(publish_ts,'%Y-%m-%d')
between 
date_sub((select
date_format(publish_ts,'%Y-%m-%d')
from (
    select
    publish_ts    
    ,row_number()over(order by publish_ts desc) rk
    from author join post on post.author_id = author.author_id
) t1
where rk = 1),interval 30 day) 
and 
(select
date_format(publish_ts,'%Y-%m-%d')
from (
    select
    publish_ts    
    ,row_number()over(order by publish_ts desc) rk
    from author join post on post.author_id = author.author_id
) t1
where rk = 1)
group by 1,2
) t2
where posts_30d > 0
order by 4 desc,3 desc,1
limit 5