好的，我有一个潜水者徽章，目前正在运行：
-- Lurkers: users who viewed topics but did not reply in the last 30 days,
-- but stop awarding (and revoke) once they’ve received ≥3 accepted answers
WITH recent_readers AS (
SELECT DISTINCT user_id
FROM user_visits
WHERE visited_at > CURRENT_DATE - INTERVAL '30 days'
AND posts_read > 0
),
recent_repliers AS (
SELECT DISTINCT user_id
FROM posts
WHERE created_at > CURRENT_DATE - INTERVAL '30 days'
AND post_number > 11
AND deleted_at IS NULL
),
recent_solutions AS (
-- users whose own topics have had ≥3 accepted answers in the last 30 days
SELECT t.user_id
FROM posts p
JOIN post_custom_fields pc
ON pc.post_id = p.id
AND pc.name = 'is_accepted_answer'
JOIN topics t
ON t.id = p.topic_id
WHERE p.created_at > CURRENT_DATE - INTERVAL '30 days'
-- exclude self-accepts if you want only answers by others:
AND p.user_id <> t.user_id
GROUP BY t.user_id
HAVING COUNT(*) >= 3
)
SELECT
u.id AS user_id,
u.username_lower AS username,
u.last_seen_at,
CURRENT_TIMESTAMP AS granted_at
FROM users u
JOIN recent_readers rr
ON rr.user_id = u.id
LEFT JOIN recent_repliers rp
ON rp.user_id = u.id
LEFT JOIN recent_solutions rs
ON rs.user_id = u.id
WHERE rp.user_id IS NULL -- never replied in last 30d
AND rs.user_id IS NULL -- hasn’t yet received 3 solutions in last 30d
AND u.active = TRUE
ORDER BY u.last_seen_at DESC
我相信它可以得到改进，而且我还没有完成测试，但我的要求是添加一个信任等级限制（到 trust_level_1），并且我明白我需要一个 API 调用或一个插件。
这项举措正在寻找插件。请发送报价，我将进行分析，快速付款和开发。