题解 | 统计创作者

-- 首先查找base_date_time
with temp as (
    select 
    max(publish_ts) as base_date_time
    from post p
    where publish_ts is not null
),

temp1 as (
    select 
    p.author_id,
    count(1) as posts_30d,
    sum(like_cnt) as likes_30d
    from post p
    cross join temp t
    where p.publish_ts >= date_sub(t.base_date_time , interval 29 day)
    and p.publish_ts <= t.base_date_time
    and p.publish_ts is not null
    group by  p.author_id
),

temp2 as (
    select 
    tp1.author_id,
    a.author_name,
    tp1.posts_30d,
    tp1.likes_30d,
    case when tp1.posts_30d = 0 then 0 else round((tp1.likes_30d / tp1.posts_30d),2) end as avg_likes_30d
    from temp1 tp1
    inner join author a
    on a.author_id = tp1.author_id
    where tp1.posts_30d > 0

)
select * from temp2
    order by likes_30d desc , posts_30d desc , author_id asc
    limit 5

系统有一点问题，有可能会报错，查找不到author这张表，不必理会。