Badge query request: Received X likes in topic containing specific tag

Community Building Data & reporting

AI-generated summary

Badge Query Request: Monthly Screenshots Participation

@erlend_sh is seeking a badge query to reward users who participate in “Monthly Screenshots” threads. The query should award a badge to users who receive more than one like on a post in a topic tagged monthly-screenshots. Alternatively, if possible, the query could check for posts containing images. @elberet proposed a SQL query that finds posts with at least two image uploads (.jpg or .png files).

1

Continuing the discussion from What cool badge queries have you come up with?.

Our forum has “Monthly Screenshots” threads. I’d like to reward the people who participate in these with a badge. I don’t know if there’s an easy way to tell if a post contains an image, but if so then that would be a welcome addition to the query.

The simplest query I can think of would be to award this badge to anyone who receives >1 like on a post in a topic tagged monthly-screenshots.

Why >1 likes? Because many people also reply to the image-posters, but these commenters very rarely receive more than 1 like (usually as a “thanks for the compliment” from an image-poster), so it’s accurate enough for my needs.

2 
select p.id, p.user_id, count(u.id)
from posts p
  left join post_uploads pu on p.id = pu.post_id
  left join uploads u on pu.upload_id = u.id 
where u.original_filename similar to '%.(jpg|png|)'
group by p.id, p.user_id
  having count(u.id) > 1

This finds all posts with at least two uploads that are images (end in either .jpg or .png).