Okay, tengo esta insignia de lurkers y está funcionando actualmente:
-- 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
Estoy seguro de que podría mejorarse y no terminé de probarlo, pero mi solicitud aquí es agregar una limitación de nivel de confianza (a trust_level_1) y entiendo que necesito una llamada a la API o un Plugin.
Esta iniciativa está buscando el plugin. Por favor, envíe una cotización y la analizaré, pago y desarrollo rápidos.