# Create a "Diary writer" badge

**URL:** https://meta.discourse.org/t/create-a-diary-writer-badge/50844
**Category:** Data & reporting
**Tags:** sql-triggered-badge
**Created:** [September 29, 2016, 3:05am UTC](https://meta.discourse.org/t/create-a-diary-writer-badge/50844 "2016-09-29T03:05:45Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![meglio](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/meglio/32/71444_2.png) [@meglio](https://meta.discourse.org/u/meglio)
#### Post date: [September 29, 2016, 3:05am UTC](https://meta.discourse.org/t/create-a-diary-writer-badge/50844/1 "2016-09-29T03:05:45Z")

</div>

> ⚠ Badge SQL is disabled by default. See: [Enable Badge SQL](https://meta.discourse.org/t/enable-badge-sql/47894)

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

```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`