Likes in a short time/in one topic

Data & reporting
1

I run a game development community and showcasing your game is always a great way to harvest Likes. As far as grand total of Likes go, spreading your updates out is probably the way to go, but the Badge system won’t take much notice of you unless you save up for bigger updates (especially as long as the default threshold is 10).

Here’s a guy who’s getting a decent amount of attention for his good work, but no badges.

What about a default badge with a query along these lines:

Likeable Poster:

30 likes accumulated over the span of 15 days

Could be one-time-award to keep things simple. Else one would have to do a reset of the count once it’s awarded (while possibly still counting on the old “score” as well in case there’s a 60-likes badge as well).

Bonus idea:

Topic Participant Extraordinaire

total of 30 likes spread over posts in the same topic
4 Likes
2 
SELECT count(*), r.username Liked, r.id user_id, current_timestamp granted_at
FROM post_actions pa
INNER JOIN posts p on p.id=pa.post_id /* Get post details */
INNER JOIN users r on r.id=p.user_id /* The user who made the post that was liked */
WHERE pa.post_action_type_id=2 /* The user who liked the post */
AND pa.created_at > CURRENT_DATE - INTERVAL '15 DAYS' /* Change to suit */
GROUP BY Liked, r.id
HAVING count(*) > 30 /* Change to suit */
ORDER BY count(*) DESC

I’d recommend running that once a day (certainly not every post edit/creation) - it takes 2 seconds to run on my backup instance, though I’m sure the query could be tweaked to make it quicker.

In which case you’ll want:

3 Likes
3 
SELECT count(*), r.username Liked, r.id user_id, current_timestamp granted_at, p.topic_id
FROM post_actions pa
INNER JOIN posts p on p.id=pa.post_id
INNER JOIN users r on r.id=p.user_id
WHERE pa.post_action_type_id=2 
GROUP BY Liked, r.id, p.topic_id
HAVING count(*) > 30
ORDER BY count(*) DESC

10 seconds. Certainly no more frequently than once a day.

2 Likes