仪表板报告 - 标志状态

数据与报告
, ,
1

这是仪表板标志状态报告的 SQL 版本。

标志状态报告旨在通过分析在指定时间段内解决被标记帖子的时间来提供对审核过程的见解。它包括有关提出的标志类型、涉及的帖子和用户、标志的创建日期、解决状态、处理标志的员工以及达到解决所需时间等详细信息。

此报告对社区经理和版主很有用，可以评估审核团队的效率，识别解决过程中的任何问题，并确保及时维护社区标准。

-- [params]
-- date :start_date = 2023-01-01
-- date :end_date = 2024-01-01

WITH period_actions AS (
    SELECT pa.id,
           pa.post_action_type_id,
           pa.created_at,
           pa.agreed_at,
           pa.disagreed_at,
           pa.deferred_at,
           pa.agreed_by_id,
           pa.disagreed_by_id,
           pa.deferred_by_id,
           pa.post_id,
           pa.user_id,
           COALESCE(pa.disagreed_at, pa.agreed_at, pa.deferred_at) AS responded_at,
           EXTRACT(EPOCH FROM (COALESCE(pa.disagreed_at, pa.agreed_at, pa.deferred_at) - pa.created_at)) / 60 AS time_to_resolution_minutes -- time to resolution in minutes
    FROM post_actions pa
    WHERE pa.post_action_type_id IN (3,4,6,7,8)
      AND pa.created_at >= :start_date
      AND pa.created_at <= :end_date
),
poster_data AS (
    SELECT pa.id,
           p.user_id AS poster_id,
           p.topic_id,
           p.post_number,
           u.username_lower AS poster_username,
           u.uploaded_avatar_id AS poster_avatar_id
    FROM period_actions pa
    JOIN posts p ON p.id = pa.post_id
    JOIN users u ON u.id = p.user_id
),
flagger_data AS (
    SELECT pa.id,
           u.id AS flagger_id,
           u.username_lower AS flagger_username,
           u.uploaded_avatar_id AS flagger_avatar_id
    FROM period_actions pa
    JOIN users u ON u.id = pa.user_id
),
staff_data AS (
    SELECT pa.id,
           u.id AS staff_id,
           u.username_lower AS staff_username,
           u.uploaded_avatar_id AS staff_avatar_id
    FROM period_actions pa
    JOIN users u ON u.id = COALESCE(pa.agreed_by_id, pa.disagreed_by_id, pa.deferred_by_id)
),
flag_types AS (
    SELECT pat.id,
           pat.id AS flag_type
    FROM post_action_types pat
)
SELECT
    CASE
        WHEN pat.flag_type = 3 THEN 'off_topic'
        WHEN pat.flag_type = 4 THEN 'inappropriate'
        WHEN pat.flag_type = 6 THEN 'notify_user'
        WHEN pat.flag_type = 7 THEN 'notify_moderators'
        WHEN pat.flag_type = 8 THEN 'spam'
    END "type",
    pa.post_id as flagged_post_id,
    pd.poster_id as poster_user_id,
    fd.flagger_id as flagger_user_id,
    DATE(pa.created_at) as flag_created,
    CASE
        WHEN pa.agreed_at IS NOT NULL THEN 'agreed'
        WHEN pa.disagreed_at IS NOT NULL THEN 'disagreed'
        WHEN pa.deferred_at IS NOT NULL THEN 'deferred'
    END "resolution",
    sd.staff_id as assigned_user_id,
    ROUND(pa.time_to_resolution_minutes,2) as "resolution_time (minutes)"
FROM period_actions pa
JOIN poster_data pd ON pd.id = pa.id
JOIN flagger_data fd ON fd.id = pa.id
LEFT JOIN staff_data sd ON sd.id = pa.id
JOIN flag_types pat ON pat.id = pa.post_action_type_id
ORDER BY pa.created_at ASC

SQL 查询说明

查询使用通用表表达式 (CTE) 构建，这些 CTE 准备了一部分数据，然后该数据在最终的 SELECT 语句中使用以编译报告。

参数

查询接受两个参数：

  • :start_date：生成报告的期间的开始日期。
  • :end_date：生成报告的期间的结束日期。

CTE

period_actions
此 CTE 选择落在指定日期范围内的帖子操作（标志），并且是特定类型（3、4、6、7、8）的，这些类型对应于不同的标志原因。它通过计算标志创建时间与响应时间（同意、不同意或延迟）之间的时间差来计算“time_to_resolution_minutes”。

poster_data
此 CTE 将 period_actions 与 posts 和 users 表连接起来，以检索有关被标记帖子的发帖人的信息，包括其用户 ID、主题 ID、帖子编号和用户名。

flagger_data
此 CTE 将 period_actions 与 users 表连接起来，以获取有关标记帖子的用户的信息，包括其用户 ID 和用户名。

staff_data
此 CTE 通过将 period_actions 与 users 表基于同意、不同意或延迟标志的员工 ID 进行连接，来检索有关处理该标志的员工的信息。

flag_types
此 CTE 仅从 post_action_types 表中选择 ID，这些 ID 将用于在最终的 SELECT 语句中确定标志的类型。

最终 SELECT
最终的 SELECT 语句将所有 CTE 组合起来，以呈现一份全面的报告。它包括以下列：

  • type：提出的标志类型，由 flag_type ID 确定。
  • flagged_post_id：被标记帖子的 ID。
  • poster_user_id：发布被标记内容的用户的 ID。
  • flagger_user_id：标记该内容的用户的 ID。
  • flag_created：创建标志的日期。
  • resolution：标志的解决状态（同意、不同意、延迟）。
  • assigned_user_id：处理该标志的员工的 ID。
  • resolution_time (minutes)：解决该标志所需的时间（以分钟为单位），四舍五入到小数点后两位。

然后，报告按标志创建日期 (pa.created_at) 的升序排序。

示例结果

type flagged_post poster_user flagger_user flag_created resolution assigned_user resolution_time (minutes)
off_topic Example_Post_Link1 example_user1 example_user2 2023-01-01 agreed example_user3 4.56
inappropriate Example_Post_Link2 example_user1 example_user2 2023-01-02 disagreed example_user3 38.76
spam Example_Post_Link3 example_user1 example_user2 2023-01-03 deferred example_user3 79.32
1 个赞