Okay, I have this lurkers badge and it’s currently working:
-- 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
I’m sure it could be improved and I didn’t finish at testing it, but my request here is to add a trust_level limitation (to trust_level_1) and I understand that I need an API call or a Plugin.
This initiative is looking for the plugin. Please send a quotation and I will analize it, fast payment and development.