Badges for X Number of Solutions Created?

data & reporting
,
1

Hey I was wondering if there was any template or standard query for awarding badges for a certain amount of solutions? For example I respond to about 10 posts/ questions and they all get marked as solutions. I get a badge for this and each corresponding badge for 20 30 40 solutions marked, etc.

I tried to look around for the standard queries but didn’t seem to be there. I do see there are some custom queries written here.

hanks,

2

There are a couple that come with the plugin which you can enable from the /admin/badges page (HelpDesk and TechSupport). But here are the queries if you want to adapt them:

SELECT p.user_id, p.id post_id, p.updated_at granted_at
FROM badge_posts p
WHERE p.post_number > 1 AND
    p.id IN (
      SELECT post_id FROM (
       SELECT pc.post_id, row_number()
       OVER (PARTITION BY p1.user_id ORDER BY pc.created_at) as rnum
       FROM post_custom_fields pc
       JOIN badge_posts p1 ON p1.id = pc.post_id
       JOIN topics t1 ON p1.topic_id = t1.id
       WHERE name = 'is_accepted_answer' AND
                    p1.user_id <> t1.user_id AND
        (
          :backfill OR
           p1.user_id IN (
                   select user_id from posts where p1.id IN (:post_ids)
           )
       )
) X  WHERE rnum = 1)


SELECT id user_id, current_timestamp granted_at
FROM users
WHERE id  IN (
       SELECT p1.user_id
       FROM post_custom_fields pc
       JOIN badge_posts p1 ON p1.id = pc.post_id
       JOIN topics t1 ON p1.topic_id = t1.id
       WHERE p1.user_id <> t1.user_id AND
                    name = 'is_accepted_answer' AND
            p1.user_id IN (
                   SELECT user_id
                   FROM posts
                   WHERE :backfill OR  p1.id IN (:post_ids)
            )
        GROUP BY p1.user_id
        HAVING COUNT(*) > 9
)
2 Likes
3

Thanks @JammyDodger,

I may need a bit of more assistance. Are there some docs you have that identifies some the selectors/fields for a solution?

Screen Shot 2022-06-06 at 11.05.29 AM

4

No worries. :slightly_smiling_face: The data explorer is really good for trying out queries before you add them as custom SQL badges (though the data explorer ones don’t need the backfill part).

Though I think the second query above has all the info in you need. The solutions are kept in the post_cutom_field table as is_accepted_answer in the name column. And that second query grants the TechSupport badge for 10 solutions (HAVING COUNT(*) > 9), so for the ones you want from the OP you just need to copy that query and change the bottom number to 19, 29, 39, and so on.

1 Like