"Personal Page" badge

Data & reporting
1

I’d like to share a badge that I developed for a contest in forum.kozovod.com.

Personal Page badge

  • Created a topic in a particular category
  • Wrote at least 1000 words in the topic (in one or multiple messages)
  • Wrote in the topic at least 10 days (non-consecutive)
  • Uploaded at least 10 images

Goal

In our forum, we have a category named “farms”. Goat farmers, small to big, are welcome to create their own topic/page there to tell about their farm, upload photos, push news and answer questions from those who buy milk or goats from them.

As part of the contest, we ask farmers to gain the “Personal Page” badge. This means they’ll have to create a topic in that category and write regularly for at least 10 days.

Hope there are many more applications for such “about me” pages, ours being just an example for a goat farmers community.

Notes

  • Words are counted with using this quick and dirty word count SQL; a side-effect of such a simplicity is that e.g. an image would count for 4 words, but that shouldn’t be an issue when you need 1000 words;
  • Images are counted with using the same simplistic approach
  • Only words and images of the topic owner are counted in the topic
  • Does not count:
    • silenced users
    • staged users
    • admins
    • archived topics
    • closed topics
    • non-visible topics (hidden from lists)
    • deleted posts
  • Run daily

SQL Query

WITH

personal_pages AS (
  SELECT *
  FROM topics
  WHERE category_id = 17
    AND archived = false
    AND closed = false
    AND visible = true
),

word_counts AS (
  SELECT p.id as post_id,
    length(raw) - length(replace(raw, ' ', ''))+1 as num
  FROM posts p
),

image_count AS (
  SELECT p.id as post_id,
    length(raw) * 2
      - length(replace(raw, '<img', '123'))
      - length(replace(raw, '[img]', '1234')) as num
  FROM posts p
),

summary AS (
  SELECT 
    pp.id as topic_id,
    pp.user_id as user_id,
    COUNT (DISTINCT p.created_at::date) as num_days,
    SUM (wc.num) as num_words,
    SUM (ic.num) as num_images
  FROM personal_pages pp
  LEFT JOIN users u on u.id = pp.user_id
  LEFT JOIN posts p ON p.topic_id = pp.id
                   AND p.user_id = pp.user_id
  LEFT JOIN word_counts wc ON wc.post_id = p.id
  LEFT JOIN image_count ic ON ic.post_id = p.id
  WHERE u.admin = false
    AND u.silenced = false
    AND u.staged = false
    AND p.deleted_at IS NULL
  GROUP BY pp.id, pp.user_id
)

SELECT summary.*,
  CURRENT_DATE as granted_at
FROM summary
WHERE num_days >= 10
  AND num_words >= 1000
  AND num_images >= 10

Adjust your numbers at the very end of the query:

WHERE num_days >= 10
  AND num_words >= 1000
  AND num_images >= 10

… and put the category ID in this row:

WHERE category_id = 17

The query outputs more fields than required by the Badges System; that is for you to use the same query in the Data Explorer:

Pasted image1105×720 180 KB

Your feedback is welcome.
Have fun!

22 Likes