题解 | 统计创作者

with temp0 as (
    select max(publish_ts) as lastest_publish_time
    from post
)

select
    t.author_id,
    author_name,
    posts_30d,
    likes_30d,
    avg_likes_30d
from
    (
        select
            p.author_id,
            count(*) as posts_30d,
            sum(like_cnt) as likes_30d,
            ifnull(round(sum(like_cnt) / count(*), 2), 0) as avg_likes_30d
        from
            post p
        where
            timestampdiff(hour,publish_ts,(select lastest_publish_time from temp0)) <= 24 * 30
        group by p.author_id
        having posts_30d > 0
    ) as t
    inner join author on t.author_id = author.author_id
order by
    likes_30d desc,
    posts_30d desc,
    t.author_id asc
limit 5;