统计创作者_牛客题霸

【背景】内容社区需要统计“创作者近30天发文表现”，用于简单的周会看板。基于作者表与帖子表，计算每位作者近30天的发文数、总点赞数、平均点赞数，并输出前5名作者。【原始表】 1)author（作者信息） author_idINT 主键 author_nameVARCHAR(64) 非空 joined_atDATE 非空 2)post（帖子表） post_idBIGINT 主键 author_idINT 非空 titleVARCHAR(255) 非空 like_cntINT 非负 publish_tsDATETIME 非空【要求】以“最新一条帖子发布日期”为统计基准日，窗口为“基准日向前 30 天（含基准日）”。对每位作者统计： posts_30d：近30天发文数 likes_30d：近30天总点赞数 avg_likes_30d：ROUND(likes_30d posts_30d, 2)（发文为 0 时记 0）最后输出近30天发文数0的作者，按likes_30d降序、posts_30d降序、author_id升序排序，取前 5 名。【示例输入】 author +-----------+-------------+------------+ author_id author_name joined_at +-----------+-------------+------------+ 1 Alice 2023-01-01 2 Bob 2023-02-01 3 Carol 2023-03-01 +-----------+-------------+------------+ post +---------+-----------+----------------------+----------+ post_id author_id publish_ts like_cnt +---------+-----------+----------------------+----------+ 1001 1 2024-08-15 10:00:00 10 1002 1 2024-08-20 09:00:00 30 1003 2 2024-08-10 12:00:00 15 1004 2 2024-08-25 08:00:00 25 1005 3 2024-07-10 09:00:00 5 +---------+-----------+----------------------+----------+ 【示例输出】 +-----------+-------------+-----------+-----------+--------------+ author_id author_name posts_30d likes_30d avg_likes_30d +-----------+-------------+-----------+-----------+--------------+ 1 Alice 2 40 20.00 2 Bob 2 40 20.00 +-----------+-------------+-----------+-----------+--------------+

示例1

输入

DROP TABLE IF EXISTS post;
DROP TABLE IF EXISTS author;

CREATE TABLE author(
  author_id INT PRIMARY KEY,
  author_name VARCHAR(64) NOT NULL,
  joined_at DATE NOT NULL
);

CREATE TABLE post(
  post_id BIGINT PRIMARY KEY,
  author_id INT NOT NULL,
  title VARCHAR(255),
  like_cnt INT NOT NULL,
  publish_ts DATETIME NOT NULL,
  INDEX idx_post_author_ts (author_id, publish_ts)
);

INSERT INTO author VALUES
(1,'Alice','2023-01-01'),
(2,'Bob','2023-02-01'),
(3,'Carol','2023-03-01');

INSERT INTO post VALUES
(1001,1,'A',10,'2024-08-15 10:00:00'),
(1002,1,'B',30,'2024-08-20 09:00:00'),
(1003,2,'C',15,'2024-08-10 12:00:00'),
(1004,2,'D',25,'2024-08-25 08:00:00'),
(1005,3,'E',5,'2024-07-10 09:00:00');

输出

author_id|author_name|posts_30d|likes_30d|avg_likes_30d
1|Alice|2|40|20.00
2|Bob|2|40|20.00