How to add a counter parameter to a Badge Query?

support
1

Hi everyone,

I have the following badge query that issues a badge to people who create a topic in a specific category that contains specific keywords:

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 'Where to fly your drone in the UK'
) AND p.post_number = 1 AND p.raw LIKE '%dronescene.co.uk%' 
GROUP BY p.user_id

This has been working fine for ages now and I’d like to take it to the next level.

How can I add a counter to this query so that I can issue a Bronze badge for people who create one such topic, a Silver badge for people who create 5 matching topics and a Gold for people who create 10 topics based on these parameters?

I realise I’d have to have one query per badge naturally, but I’m unsure how to add a counter to this query.

Can anyone advise?

Thanks in advance :+1:t2:

2

I think I’ve just answered my own question by adding this on the end of the query:

HAVING count(*) > 4

Just need to confirm / check the proposed results.

5 Likes
3

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.