Gamification Calibration

This is a query that replicates the Gamification scores and gives a breakdown of who got what and why:

-- [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(tcf.topic_id) AS solutions,
        COUNT(tcf.topic_id) * :solutions_score_value AS solutions_score
    FROM topic_custom_fields tcf
      INNER JOIN topics t ON tcf.topic_id = t.id
      INNER JOIN posts p ON p.id = tcf.value::INTEGER
    WHERE p.deleted_at IS NULL 
      AND t.deleted_at IS NULL 
      AND tcf.name = 'accepted_answer_post_id' 
      AND t.archetype <> 'private_message' 
      AND p.user_id <> t.user_id 
      AND tcf.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

There is a drift to the scores the longer the timeframe as the proper Gamification version rounds out things like Time and Posts read on a daily basis, whereas this will tally up and include all the remainders. However, it is still quite useful to check out what any changes to your scoring defaults would look like without having to test it out by recalculating all of your site’s live scores.

It also doesn’t include anyone in the @staff group, though this is easy enough to change if you want to. :+1: :slight_smile:

Depending on the size of your site, you may need to reduce the timescale or groups you target to stop it from timing out.

1 Like