هذا استعلام يكرر نقاط 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(st.topic_id) AS solutions,
COUNT(st.topic_id) * :solutions_score_value AS solutions_score
FROM discourse_solved_solved_topics st
INNER JOIN topics t ON st.topic_id = t.id
INNER JOIN posts p ON p.id = st.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 st.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
هناك انحراف في النقاط كلما زادت الفترة الزمنية، حيث أن النسخة الأصلية من Gamification تقوم بتقريب أشياء مثل الوقت والمشاركات المقروءة على أساس يومي، بينما سيقوم هذا بتجميع وتضمين جميع الباقي. ومع ذلك، لا يزال من المفيد جدًا التحقق من كيف ستبدو أي تغييرات على افتراضات التسجيل الخاصة بك دون الحاجة إلى اختبارها عن طريق إعادة حساب جميع نقاط موقعك الحية.
كما أنه لا يتضمن أي شخص في مجموعة @staff، على الرغم من أن هذا سهل التغيير إذا كنت ترغب في ذلك. 
اعتمادًا على حجم موقعك، قد تحتاج إلى تقليل النطاق الزمني أو المجموعات التي تستهدفها لمنع انتهاء المهلة.
