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