D’accord, j’ai ce badge lurkers et il fonctionne actuellement :
-- 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
Je suis sûr que cela pourrait être amélioré et je n’ai pas fini de le tester, mais ma demande ici est d’ajouter une limitation de niveau de confiance (à trust_level_1) et je comprends que j’ai besoin d’un appel API ou d’un plugin.
Cette initiative recherche le plugin. Veuillez envoyer un devis et je l’analyserai, paiement et développement rapides.