题解 | 统计创作者

WITH t AS ( SELECT author_id, max( publish_ts ) AS fst FROM post GROUP BY 1 ) SELECT
p.author_id,
author_name,
count(*) AS posts_30d,
sum( like_cnt ) AS likes_30d,
COALESCE ( round( sum( like_cnt )/ count(*), 2 ), 0 ) AS avg_likes_30d 
FROM
	post p
	LEFT JOIN author a ON p.author_id = a.author_id
	LEFT JOIN t ON t.author_id = p.author_id 
WHERE
	publish_ts BETWEEN DATE_sub( fst, INTERVAL 29 DAY ) 
	AND fst 
GROUP BY
	1
order by 4 desc,3 desc,1
limit 5