Hello everyone! Hope things are well for you!
The scenario: a monthly giveaway intended to boost board traffic and participation. The winner is based on total Likes received during the competition timeframe, let's say one month. Participation in the giveaway is optional, and staff members ineligible, so not every member will count. The giveaway will be recurring monthly. Participants could be entered into a member group.
The question: how can the winner be determined in an automated fashion either including the participants or excluding certain members, whichever is easier?
The total likes per member over a given time period would be this query, just run this query it in your myphpadmin or adminer
SELECT
lp.id_poster, lp.like_count,
COALESCE(mem.real_name, m.poster_name) AS real_name, mem.posts
FROM (
SELECT
id_poster, COUNT(id_msg) AS like_count, MAX(id_msg) AS id_msg
FROM elkarte_message_likes
WHERE id_poster != 0
AND like_timestamp BETWEEN UNIX_TIMESTAMP("2022/12/01") AND UNIX_TIMESTAMP("2022/12/31 23:59:59")
GROUP BY id_poster
ORDER BY like_count DESC
LIMIT 10
) AS lp
INNER JOIN elkarte_messages AS m ON (m.id_msg = lp.id_msg)
INNER JOIN elkarte_members AS mem ON (mem.id_member = m.id_member)
LIMIT 10
That will give the top ten for the month defined, in this case December. That just a pure count of likes, so can be gigged :wink: e.g. two members simply post reply's and like each others messages.
You could also do a by unique likers on a topic in a given period, so if one member likes every post in a topic of another member, it would only count as one. If you want that query let me know.
You're a genius spuds! You should write software or something. :cheesy:
Thanks man!
Now there's an idea that can get out of hand in a hurry.. 🤸♀️🥳🙌🎂 🍰🎈🎉 🎁 🤣
Might consider creating a new board, and confining the contest to that board, one that can be deleted later.. 😏 😜
Here's a brief followup.
@Spuds your query code worked wonderfully! Thanks again!
Awesome :cheesy:
Sure is! Now to launch the monthly giveaways!