Cooties engaged: Part trois - INB4 badges
-
Continuing the discussion from Cooties engaged: Part Deux:
@fbmac said:
@abarker could setup a script to keep closing and opening the topic, and give some gamification badges for people that can get their posts between the closed and opened messages. That should work.
Actually, the INB4 badges require that the topic be flagged with a special GUID by an authorized badge grantor who also happens to be an admin. Also, they cannot be granted in One Post.
And they only get awarded for the first closure. I think. Might be the last (while withdrawing previous ones.)
WITH auto_closed_topics AS ( /* this clause will change */ SELECT user_id, id, topic_id, post_number FROM posts WHERE raw LIKE '%UUID%' AND user_id IN ( SELECT gu.user_id FROM group_users gu WHERE group_id IN( SELECT g.id FROM groups g WHERE g.name IN ('badge_granters_admin') ) ) ), closing_post AS ( SELECT MAX(post_number), id post_id, topic_id, created_at FROM posts p WHERE topic_id IN ( SELECT topic_id FROM auto_closed_topics ) AND post_type=3 AND raw ILIKE 'This topic was automatically closed after %' GROUP BY id, topic_id, created_at ), nate_posts AS ( SELECT p.user_id, p.post_number, p.id post_id, p.topic_id, p.created_at, p.raw, row_number() OVER (PARTITION BY p.topic_id ORDER BY p.created_at DESC) as rank FROM posts p JOIN closing_post cp ON p.topic_id=cp.topic_id AND p.created_at > cp.created_at - INTERVAL '1 minute' AND p.id != cp.post_id ) SELECT user_id, post_id, created_at granted_at FROM nate_posts WHERE rank=2
That last
rank=2
determines the badge, 1 for gold, 2 silver, 3 bronze.Either way it would certainly only be one set of badges per topic.
In fact I've not tested that since they changed the style of the closing posts to see if they've broken things under the hood.
-
Reading that, I don't see why you couldn't get multiple closing_posts per topic. Where every post less than 1 minute before or any time after the first closing post gets one badge. Every post less than 1 minute before or any time after the second closing post gets two badges. Etc.
-
Because it orders the posts by time, gives them a rank based on row number and only awards a badge to row 1 (or 2, or 3.)
You can't have more than one row 1 (or 2, or 3.)
-
In fact I've not tested that since they changed the style of the closing posts to see if they've broken things under the hood.
IIRC they did and they needed fixing to regrant the ones granted previously.
edit: No, that was the renaming of the groups.
-
Right, missed that bit. A post can still be in nate_posts multiple times, but the ranking happens per topic regardless of which closing post selected the row. So extra posts in such a topic after the close (or a reopen) would cause badges to move. A second close could cause duplicate badges, depending on how do analytic functions in postgres handle duplicates?