报告的记录

1113. 报告的记录

动作表：Actions

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| post_id       | int     |
| action_date   | date    | 
| action        | enum    |
| extra         | varchar |
+---------------+---------+
此表可能会有重复的行。
action 字段是 ENUM 类型的，包含:('view', 'like', 'reaction', 'comment', 'report', 'share')
extra 包含关于 action 的可选信息，例如举报的原因或反馈的类型。
当 action 为 'report' 时 extra 不会为 NULL。

编写解决方案，针对每个举报原因统计昨天的举报帖子数量。假设今天是 2019-07-05 。

返回结果表 无顺序要求 。

结果格式如下示例所示。

示例 1：

输入：
Actions table:
+---------+---------+-------------+--------+--------+
| user_id | post_id | action_date | action | extra  |
+---------+---------+-------------+--------+--------+
| 1       | 1       | 2019-07-01  | view   | null   |
| 1       | 1       | 2019-07-01  | like   | null   |
| 1       | 1       | 2019-07-01  | share  | null   |
| 2       | 4       | 2019-07-04  | view   | null   |
| 2       | 4       | 2019-07-04  | report | spam   |
| 3       | 4       | 2019-07-04  | view   | null   |
| 3       | 4       | 2019-07-04  | report | spam   |
| 4       | 3       | 2019-07-02  | view   | null   |
| 4       | 3       | 2019-07-02  | report | spam   |
| 5       | 2       | 2019-07-04  | view   | null   |
| 5       | 2       | 2019-07-04  | report | racism |
| 5       | 5       | 2019-07-04  | view   | null   |
| 5       | 5       | 2019-07-04  | report | racism |
+---------+---------+-------------+--------+--------+
输出：
+---------------+--------------+
| report_reason | report_count |
+---------------+--------------+
| spam          | 1            |
| racism        | 2            |
+---------------+--------------+ 
解释：注意，我们只关心举报帖数量非零的举报原因。

原站题解

去查看

上次编辑到这里，代码来自缓存点击恢复默认模板

# Write your MySQL query statement below

mysql 解法, 执行用时: 476 ms, 内存消耗: 0 B, 提交时间: 2023-10-15 17:05:34

select extra as report_reason, count(distinct post_id) as report_count
from actions
where datediff('2019-07-05',action_date) = 1
    and extra is not null
    and action ='report'
group by report_reason;

mysql 解法, 执行用时: 589 ms, 内存消耗: 0 B, 提交时间: 2023-10-15 17:05:20

# Write your MySQL query statement below
SELECT extra AS 'report_reason', COUNT(DISTINCT(post_id)) AS 'report_count'
FROM Actions
WHERE extra IS NOT NULL             #非空
    AND action = 'report'           #是报告活动
    AND action_date = '2019-07-04'  #昨天
GROUP BY extra
;

mysql 解法, 执行用时: 692 ms, 内存消耗: 0 B, 提交时间: 2023-10-15 17:05:09

# Write your MySQL query statement below
select extra as report_reason, count(distinct post_id) as report_count 
from Actions
where action_date ='2019-07-04'
and action='report '
and extra  is not null
group by extra

上一题

下一题

详情