What cool badge queries have you come up with?

Awesome list. If anyone interested I’ve created on my community an extra badge " Community Hero" for users having 30+ already granted badges. More info here.

1 Like

Hey guys!

I’m trying some badge queries out here :slight_smile: Some are working fine and others I’m still trying out.

Anyone konw how could I create a query that would grant a badge to a user that has X views in topics created in a specific category? I was able to build this query, but for a topic (User got at least X views in a topic in a specific category). The code is:

SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE category_id = (
  SELECT id FROM categories WHERE name ilike 'Technology'
) AND p.post_number = 1
AND views >= 10
and (:backfill OR ( p.id IN (:post_ids) ))
GROUP BY p.user_id

But instead of only one topic, I would like to grant the badge for those who get, for example, 500 views in the sum of views from all the topics that person created in the category Y. Anyone knows how could I achieve this? :smiley:

1 Like

Just tried to achieve this creating queries on Metabase, but couldn’t find a solution. Will keep trying out :slight_smile:

2 Likes

Maybe this is a solution (further tests coming :D)

    SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE category_id = (
  SELECT id FROM categories WHERE name ilike 'Technology'
) AND p.post_number = 1
and (:backfill OR ( p.id IN (:post_ids) ))
GROUP BY p.user_id
HAVING SUM (views) > XXXXXXXXXXX
3 Likes

Hi guys, I managed to test this query up there and it worked! Now, I was wondering if anyone can help me with another challenge. This is the query:

SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE category_id = (
  SELECT id FROM categories WHERE name ilike 'Arts'
) AND p.post_number = 1
and (:backfill OR ( p.id IN (:post_ids) ))
GROUP BY p.user_id
HAVING SUM (views) >= 100

On this example: The user who creates stories in the Arts category and those stories reach 100 views, will receive the badge. The problem is that:
I will need to change this logic over time, because the number of views will increase.
This badge requires 100 views today, but can require 1000 views 6 months from now.

The problem is that when I change the query, it runs again every day and the users who already received it would lose it.

And this would be a disaster, imagine that you reached (now at the beginning of the platform), 10 views and won a Level 2 Badge in Technology. But some months later the platform grows and now Level 2 requires no more 10 views, but 100. You would lose the badge.

There is a way I could prevent this to happen?

1 Like