이 쿼리는 게임화(Gamification) 점수를 재현하고, 누가 어떤 점수를 받았으며 그 이유는 무엇인지에 대한 세부 내역을 제공합니다:
-- [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 "Total Cheers",
COALESCE(v.user_visits,0) || ' (' || COALESCE(v.visits_score,0) || ')' AS "Visits (cheers)",
COALESCE(tr.time_read,0) || 'hrs' || ' (' || COALESCE(tr.time_read_score,0) || ')' AS "Time Read
(cheers)",
COALESCE(pr.posts_read,0) || ' (' || COALESCE(pr.posts_read_score,0) || ')' AS "Posts Read
(cheers)",
COALESCE(pc.posts_created,0) || ' (' || COALESCE(pc.posts_created_score,0) || ')' AS "Posts Created
(cheers)",
COALESCE(tc.topics_created,0) || ' (' || COALESCE(tc.topics_created_score,0) || ')'AS "Topics
Created (cheers)",
COALESCE(lr.likes_received,0) || ' (' || COALESCE(lr.likes_received_score,0) || ')' AS "Likes
Received (cheers)",
COALESCE(lg.likes_given,0) || ' (' || COALESCE(lg.likes_given_score,0) || ')' AS "Likes Given
(cheers)",
COALESCE(s.solutions,0) || ' (' || COALESCE(s.solutions_score,0) || ')'AS "Solutions (cheers)",
COALESCE(f.flags,0) || ' (' || COALESCE(f.flags_score,0) || ')' AS "Agreed Flags (cheers)",
COALESCE(i.invites,0) || ' (' || COALESCE(i.invites_score,0) || ')' AS "Invites Redeemed (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 "Total Cheers" DESC
LIMIT 100
기간이 길어질수록 점수 사이에 오차가 발생합니다. 공식적인 게임화 버전은 '읽은 시간’과 ‘읽은 게시글 수’ 등을 일일 기준으로 반올림하여 처리하지만, 이 쿼리는 모든 잔여 값을 합산하여 포함하기 때문입니다. 그럼에도 불구하고, 사이트의 실시간 점수를 모두 다시 계산하여 테스트해 볼 필요 없이 점수 기본값을 변경했을 때 어떤 결과가 나올지 확인하는 데 여전히 매우 유용합니다.
또한 @staff 그룹에 속한 사용자는 포함되지 않지만, 원하시는 경우 쉽게 변경할 수 있습니다. 
사이트의 규모에 따라 타임스케일이나 대상 그룹을 줄여 타임아웃이 발생하는 것을 방지해야 할 수 있습니다.