Total views across multiple topics

Data & reporting
1

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
2

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
3

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