هذا استعلام يعيد إنتاج درجات التلعيب (Gamification) ويوفر تفصيلاً لمن حصل على ماذا ولماذا:
-- [المعلمات]
-- 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 "إجمالي التشجيعات",
COALESCE(v.user_visits,0) || ' (' || COALESCE(v.visits_score,0) || ')' AS "الزيارات (تشجيعات)",
COALESCE(tr.time_read,0) || 'ساعة' || ' (' || COALESCE(tr.time_read_score,0) || ')' AS "الوقت المقروء
(تشجيعات)",
COALESCE(pr.posts_read,0) || ' (' || COALESCE(pr.posts_read_score,0) || ')' AS "المشاركات المقروءة
(تشجيعات)",
COALESCE(pc.posts_created,0) || ' (' || COALESCE(pc.posts_created_score,0) || ')' AS "المشاركات المنشورة
(تشجيعات)",
COALESCE(tc.topics_created,0) || ' (' || COALESCE(tc.topics_created_score,0) || ')'AS "المواضيع
المنشورة (تشجيعات)",
COALESCE(lr.likes_received,0) || ' (' || COALESCE(lr.likes_received_score,0) || ')' AS "الإعجابات
المستلمة (تشجيعات)",
COALESCE(lg.likes_given,0) || ' (' || COALESCE(lg.likes_given_score,0) || ')' AS "الإعجابات الممنوحة
(تشجيعات)",
COALESCE(s.solutions,0) || ' (' || COALESCE(s.solutions_score,0) || ')'AS "الحلول (تشجيعات)",
COALESCE(f.flags,0) || ' (' || COALESCE(f.flags_score,0) || ')' AS "الأعلام المتفق عليها (تشجيعات)",
COALESCE(i.invites,0) || ' (' || COALESCE(i.invites_score,0) || ')' AS "المدعوات المستردة (تشجيعات)"
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 "إجمالي التشجيعات" DESC
LIMIT 100
يحدث انحراف في الدرجات كلما طالت الفترة الزمنية، حيث تقوم نسخة التلعيب الرسمية بتقريب قيم مثل الوقت والمشاركات المقروءة على أساس يومي، بينما يقوم هذا الاستعلام بتجميع جميع الأجزاء المتبقية. ومع ذلك، يظل مفيدًا جدًا للتحقق من مظهر أي تغييرات على قيم التقييم الافتراضية لديك دون الحاجة إلى تجربتها عن طريق إعادة حساب جميع درجات الموقع الحية.
كما أنه لا يشمل أي شخص في مجموعة @staff، رغم أن هذا سهل التغيير إذا أردت ذلك. 
اعتمادًا على حجم موقعك، قد تحتاج إلى تقليل النطاق الزمني أو المجموعات المستهدفة لمنع حدوث انتهاء مهلة للوقت.
