根据活动创建一些群组的查询

数据与报告
10

在这种情况下，我认为类似这样的东西可以提供手动查找：

-- [params]
-- int :likes_received
-- int :posts_read


WITH user_activity AS (

    SELECT
        p.user_id,
        COUNT(p.id) as posts_count
    FROM posts p
    LEFT JOIN topics t ON t.id = p.topic_id
    WHERE p.created_at::date >= CURRENT_DATE - INTERVAL '1 YEAR'
        AND t.deleted_at IS NULL
        AND p.deleted_at IS NULL
        AND t.archetype = 'regular'
    GROUP BY 1
)

SELECT
    us.user_id,
    us.likes_received,
    us.posts_read_count,
    ua.posts_count
FROM user_stats us
  JOIN user_activity ua ON UA.user_id = us.user_id
WHERE us.likes_received >= :likes_received
  AND us.posts_read_count >= :posts_read
  AND ua.posts_count >= 10
ORDER BY 2 DESC, 3 DESC, 4 DESC

并且对其进行调整/精简以仅包含用户名，可以生成一个列表，您可以将其复制并粘贴到组页面的“添加用户”框中，如果您将结果导出为 CSV（并在记事本等中打开它）：

-- [params]
-- int :likes_received
-- int :posts_read


WITH user_activity AS (

    SELECT
        p.user_id,
        COUNT(p.id) as posts_count
    FROM posts p
    LEFT JOIN topics t ON t.id = p.topic_id
    WHERE p.created_at::date >= CURRENT_DATE - INTERVAL '1 YEAR'
        AND t.deleted_at IS NULL
        AND p.deleted_at IS NULL
        AND t.archetype = 'regular'
    GROUP BY 1
)

SELECT
    u.username
FROM user_stats us
  JOIN user_activity ua ON UA.user_id = us.user_id
  JOIN users u ON u.id = us.user_id
WHERE us.likes_received >= :likes_received
  AND us.posts_read_count >= :posts_read
  AND ua.posts_count >= 10
ORDER BY 1

这也有可能。 :partying_face: 您将需要一个徽章（和一个徽章查询）对应一个组，以及一个伴随的 自动化 使用“通过徽章的用户组会员资格”脚本。您也可以通过启用自定义触发徽章（Enable Badge SQL 和 https://meta.discourse.org/t/create-triggered-custom-badge-queries/19336）来自动授予徽章，而不是手动授予。

不过，有很多环节需要注意，所以您可能希望在此阶段保持简单。

2 个赞