Dies ist eine Abfrage, die die Gamification-Punkte repliziert und eine Aufschlüsselung liefert, wer was und warum erhalten hat:
-- [params]
-- date :start_date
-- date :end_date
-- int :day_visited_score_value = 1
-- int :time_read_score_value = 1
-- int :posts_read_score_value = 1
-- int :posts_created_score_value = 2
-- int :topics_created_score_value = 5
-- int :likes_received_score_value = 1
-- int :likes_given_score_value = 1
-- int :solutions_score_value = 10
-- int :flag_created_score_value = 10
-- int :user_invited_score_value = 10
WITH visits AS (
SELECT
uv.user_id,
COUNT(*) AS user_visits,
COUNT(*) * :day_visited_score_value AS visits_score
FROM user_visits uv
WHERE uv.visited_at BETWEEN :start_date AND :end_date
GROUP BY uv.user_id
),
time_read AS (
SELECT
uv.user_id,
SUM(uv.time_read) /3600 AS time_read,
SUM(uv.time_read) /3600 * :time_read_score_value AS time_read_score
FROM user_visits uv
WHERE uv.visited_at BETWEEN :start_date AND :end_date
AND uv.time_read >= 60
GROUP BY uv.user_id
),
posts_read AS (
SELECT
uv.user_id,
SUM(uv.posts_read) AS posts_read,
SUM(uv.posts_read) /100 * :posts_read_score_value AS posts_read_score
FROM user_visits uv
WHERE uv.visited_at BETWEEN :start_date AND :end_date
AND uv.posts_read >= 5
GROUP BY uv.user_id
),
posts_created AS (
SELECT
p.user_id,
COUNT(*) AS posts_created,
COUNT(*) * :posts_created_score_value AS posts_created_score
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id
WHERE p.deleted_at IS NULL
AND t.archetype <> 'private_message'
AND p.wiki IS FALSE
AND p.created_at::date BETWEEN :start_date AND :end_date
GROUP BY p.user_id
),
topics_created AS (
SELECT
t.user_id,
COUNT(*) AS topics_created,
COUNT(*) * :topics_created_score_value AS topics_created_score
FROM topics t
WHERE t.deleted_at IS NULL
AND t.archetype <> 'private_message'
AND t.created_at::date BETWEEN :start_date AND :end_date
GROUP BY t.user_id
),
likes_received AS (
SELECT
p.user_id,
COUNT(*) AS likes_received,
COUNT(*) * :likes_received_score_value AS likes_received_score
FROM post_actions pa
INNER JOIN posts p ON p.id = pa.post_id
INNER JOIN topics t ON t.id = p.topic_id
WHERE p.deleted_at IS NULL
AND t.archetype <> 'private_message'
AND p.wiki IS FALSE
AND post_action_type_id = 2
AND pa.created_at::date BETWEEN :start_date AND :end_date
GROUP BY p.user_id
),
likes_given AS (
SELECT
pa.user_id AS user_id,
COUNT(*) AS likes_given,
COUNT(*) * :likes_given_score_value AS likes_given_score
FROM post_actions pa
INNER JOIN posts p ON p.id = pa.post_id
INNER JOIN topics t ON t.id = p.topic_id
WHERE p.deleted_at IS NULL
AND t.archetype <> 'private_message'
AND p.wiki IS FALSE
AND post_action_type_id = 2
AND pa.created_at::date BETWEEN :start_date AND :end_date
GROUP BY pa.user_id
),
solutions AS (
SELECT
p.user_id,
COUNT(ta.answer_post_id) AS solutions,
COUNT(ta.answer_post_id) * :solutions_score_value AS solutions_score
FROM discourse_solved_solved_topics st
JOIN discourse_solved_topic_answers ta ON ta.solved_topic_id = st.id
INNER JOIN topics t ON st.topic_id = t.id
INNER JOIN posts p ON p.id = ta.answer_post_id
WHERE p.deleted_at IS NULL
AND t.deleted_at IS NULL
AND t.archetype <> 'private_message'
AND p.user_id <> t.user_id
AND ta.updated_at::date BETWEEN :start_date AND :end_date
GROUP BY p.user_id
),
flags AS (
SELECT
r.created_by_id AS user_id,
COUNT(*) AS flags,
COUNT(*) * :flag_created_score_value AS flags_score
FROM reviewables r
WHERE created_at::date BETWEEN :start_date AND :end_date
AND status = 1
GROUP BY user_id
),
invites AS (
SELECT
inv.invited_by_id AS user_id,
SUM(inv.redemption_count) AS invites,
(SUM(inv.redemption_count) * :user_invited_score_value)::int AS invites_score
FROM invites inv
WHERE inv.created_at::date BETWEEN :start_date AND :end_date
AND inv.redemption_count > 0
GROUP BY inv.invited_by_id
)
SELECT
u.id AS user_id,
(
COALESCE(v.visits_score,0) +
COALESCE(tr.time_read_score,0) +
COALESCE(pr.posts_read_score,0) +
COALESCE(pc.posts_created_score,0) +
COALESCE(tc.topics_created_score,0) +
COALESCE(lr.likes_received_score,0) +
COALESCE(lg.likes_given_score,0) +
COALESCE(s.solutions_score,0) +
COALESCE(f.flags_score,0) +
COALESCE(i.invites_score,0)
) AS „Gesamt-Cheers",
COALESCE(v.user_visits,0) || ' (' || COALESCE(v.visits_score,0) || ')' AS „Besuche (Cheers)",
COALESCE(tr.time_read,0) || 'Std' || ' (' || COALESCE(tr.time_read_score,0) || ')' AS „Gelesene Zeit
(Cheers)",
COALESCE(pr.posts_read,0) || ' (' || COALESCE(pr.posts_read_score,0) || ')' AS „Gelesene Beiträge
(Cheers)",
COALESCE(pc.posts_created,0) || ' (' || COALESCE(pc.posts_created_score,0) || ')' AS „Erstellte Beiträge
(Cheers)",
COALESCE(tc.topics_created,0) || ' (' || COALESCE(tc.topics_created_score,0) || ')' AS „Erstellte
Themen (Cheers)",
COALESCE(lr.likes_received,0) || ' (' || COALESCE(lr.likes_received_score,0) || ')' AS „Erhaltene
Likes (Cheers)",
COALESCE(lg.likes_given,0) || ' (' || COALESCE(lg.likes_given_score,0) || ')' AS „Gegebene
Likes (Cheers)",
COALESCE(s.solutions,0) || ' (' || COALESCE(s.solutions_score,0) || ')' AS „Lösungen (Cheers)",
COALESCE(f.flags,0) || ' (' || COALESCE(f.flags_score,0) || ')' AS „Zugestimmte Flaggen (Cheers)",
COALESCE(i.invites,0) || ' (' || COALESCE(i.invites_score,0) || ')' AS „Eingelöste Einladungen (Cheers)"
FROM users u
LEFT JOIN visits v ON v.user_id = u.id
LEFT JOIN posts_read pr USING (user_id)
LEFT JOIN time_read tr USING (user_id)
LEFT JOIN flags f USING (user_id)
LEFT JOIN posts_created pc USING (user_id)
LEFT JOIN topics_created tc USING (user_id)
LEFT JOIN likes_given lg USING (user_id)
LEFT JOIN likes_received lr USING (user_id)
LEFT JOIN solutions s USING (user_id)
LEFT JOIN invites i USING (user_id)
WHERE u.id > 0
AND u.id NOT IN (SELECT user_id FROM group_users WHERE group_id = 3)
ORDER BY „Gesamt-Cheers" DESC
LIMIT 100
Je länger der Zeitraum ist, desto stärker weichen die Punkte von der offiziellen Gamification-Version ab, da diese Dinge wie gelesene Zeit und gelesene Beiträge täglich rundet, während diese Abfrage alle Reste addiert und einbezieht. Dennoch ist sie sehr nützlich, um zu prüfen, wie Änderungen an Ihren Standard-Wertungen aussehen würden, ohne alle Live-Punkte Ihrer Seite neu berechnen zu müssen.
Sie enthält zudem niemanden aus der Gruppe @staff, dies lässt sich jedoch bei Bedarf leicht ändern. 
Je nach Größe Ihrer Seite müssen Sie möglicherweise den Zeitraum oder die Zielgruppen reduzieren, um Timeouts zu vermeiden.
