Create a "Diary writer" badge

Data & reporting
1

:warning: Badge SQL is disabled by default. See: Enable Badge SQL

In our goat farmers forum, people love to write Diaries about anything - a long-term experiment, travelling experience, their new hobby etc.

So we developed a “Dairy writer” badge that works as follows:

  • granted to topics with a ‘diary’ tag only
  • the topic must contain at least 3000 words
  • the topic must have received at least 50 likes (counting all messages)
  • the topic must have been viewed at least 300 times (this I recommend to tune depending on your community activity)
  • the author must have been posting to the topic for at least 15 days
  • at least 3 users with truest level 2 or higher must have posted to the topic (topic author excepted)

Badge SQL

WITH

/* SELECTS ONLY TOPIC IDs WITH A "DIARY" TAG */

diary_page_ids AS (
    SELECT topic_id
    FROM topic_tags
    WHERE tag_id = (
        SELECT id as tag_id FROM tags WHERE name = 'diary'
    )
),


/* SELECTS HOW MANY DAYS THE TOPIC AUTHOR
   HAS BEEN POSTING IN A TOPIC */
   
days_author_posted_in_their_topic AS (
    SELECT t.id as topic_id,
        COUNT (DISTINCT p.created_at::date) as num_days
    FROM topics t
    LEFT JOIN posts p ON p.topic_id = t.id
    WHERE p.user_id = t.user_id /* author's posts only */
    GROUP BY t.id
),


/* SELECTS HOW MANY USERS L2 POSTED IN A TOPIC,
   TOPIC AUTHOR EXCEPTED */
   
num_participants_L2 AS (
    SELECT t.id as topic_id,
        COUNT (DISTINCT p.user_id) as num
    FROM topics t
    LEFT JOIN posts p ON p.topic_id = t.id
    LEFT JOIN users u ON p.user_id = u.id
    WHERE p.user_id != t.user_id /* author's posts excepted */
        AND u.trust_level >= 2
    GROUP BY t.id
)

SELECT
    t.id         as topic_id,   
    u.id         as user_id,
    CURRENT_DATE as granted_at,
    
    /* FOR INFORMATION ONLY, NOT USED BY THE BADGE SYSTEM */
    days_posted.num_days as days_author_posted,
    num_participants_L2.num as L2_participants,
    t.views as views,
    t.like_count as likes,
    t.word_count as word_count
    
FROM      topics t
LEFT JOIN users u
    ON u.id = t.user_id
LEFT JOIN days_author_posted_in_their_topic days_posted
    ON days_posted.topic_id = t.id
LEFT JOIN num_participants_L2
    ON num_participants_L2.topic_id = t.id

WHERE
    /* TOPIC CLAUSES */
        t.archived = false
    AND t.closed = false
    AND t.visible = true
    AND t.deleted_at IS NULL
    AND t.word_count >= 3000
    AND t.views >= 300
    AND t.like_count >= 50
    AND t.archetype = 'regular'
    
    /* USER CLAUSES */
    AND u.blocked = false
    AND u.staged = false
    AND u.active = true

    /* TOPIC IS A DIARY */
    AND t.id IN (SELECT * FROM diary_page_ids)
    
    /* THE TOPIC's AUTHOR HAS BEEN POSTING TO THE TOPIC 15+ DAYS */
    AND days_posted.num_days >= 15
    
    /* AT LEAST 3 USERS L2 POSTED EXCEPT THE AUTHOR */
    AND num_participants_L2.num >= 3

Tuning the SQL query

  • Change 'diary' to the actual name of your tag attached to all diary topics
  • Change the following numbers to what you need:
    • AND days_posted.num_days >= 15
    • AND t.word_count >= 3000
    • AND t.views >= 300
    • AND t.like_count >= 50
    • AND num_participants_L2.num >= 3
15 Likes