题解 | 统计创作者

SELECT
    a.author_id,
    a.author_name,
    COUNT(p.post_id) AS posts_30d,
    SUM(p.like_cnt) AS likes_30d,
    ROUND(SUM(p.like_cnt) / COUNT(p.post_id), 2) AS avg_likes_30d
FROM
    author AS a
    INNER JOIN post AS p ON a.author_id = p.author_id
WHERE
    p.publish_ts BETWEEN 
    #求起始日期
    DATE_SUB(
        (
            SELECT
                max(publish_ts)
            FROM
                post
        ),
        INTERVAL 29 DAY
    ) 
    AND 
    #求最新日期
    (
        SELECT
            max(publish_ts)
        FROM
            post
    )
GROUP BY
    a.author_id,
    a.author_name
HAVING
    COUNT(p.post_id)>0
ORDER BY
    likes_30d DESC,
    posts_30d DESC,
    a.author_id
LIMIT 5;