统计创作者_

【背景】
内容社区需要统计“创作者近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 |
+-----------+-------------+-----------+-----------+--------------+

输入

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');

统计创作者

输入

输出

问题信息

热门推荐

相关试题