Project description (max 200 words): Gamification is used to motivate and engage members by creating playful experiences. To put it as simply as possible: Gamification (in any context!) uses an understanding of human psychology to elicit emotions and positive experiences, providing people with an incentive to participate. Your personal background within the context of the project: Member of several open-source communities that use gamification to motivate people, such as the Fedora Project. Estimated timeline in terms of time boxed deliverables: We should keep it simple, not creating too many rewards, but make it worthwhile to own them and keep it special. I suggest we introduce 5 custom badges for the TZM Community. Resources needed or already arranged: SQL knowledge and creativity.
I think the Project Hero badge could use some work. I think someone would need to have created 5 Projects and closed these as well. Finalized projects count more than just starting ideas
I’ll finetune the SQL query a bit more once I have more time. But people are free to help out and improve these queries already. These queries are not site specific. You can create them on any Discourse website (as an admin). There are only a few minor adjustments needed for topic/user/category ID and such.
Below is the current query. What’s missing is a check for solved topics and then conclude with a count of 5.
SELECT DISTINCT ON (t.user_id) t.user_id, t.created_at granted_at
FROM topics t
WHERE t.category_id = 28
Feel free to comment here with feedback, ideas and worked out examples!
Another idea might be that someone else solved 5 Support topics, inspired by the Helping Hand badge of the Fedora community.
Tagging the @it-team to make use of our resources (skills and time)
It’s not for all, but I think it’s nice to be surprised sometimes with a badge. I also don’t hunt for them in the Fedora community, but it’s nice to get one for activities It’s sort of cheap recognition. But not like the video below
So we have 3 now. A 4th and 5th one could be about finalizing a Approved and helping with the Reviews
I guess that concludes the goal of this project. I’ll check the database when I have more time for this and create the queries. In the meantime, people can provide feedback with improved ideas or totally different ones.
This query below finds someone who had 10 solved issues. Inspired by the Helping Hand badge in the Fedora community.
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
)
Also created a badge for completing a co-initiative. I think I’ll stop there. There is of course still the review contributor badge. But it’s hard to distinguish if someone really contributed, or maybe just posted BS.
Whoops, I was experimenting with queries and now the New User of the Month is purged except for me in there (I guess because I izz admin?). Dunno. I believe I saw @kublermdk, @BoQsc, @sabtu, @Bahusson and @Jay131278 in there. I’ll add you guys to it manually. I’ll stop messing with the badges now as well
Although I’m super busy, I couldn’t help myself. I also created a query for the co-initiative review process. The way I differentiate between valuable and not so valuable comments is when these comments are liked by people with TL3.
SELECT distinct p.user_id, p.created_at granted_at, p.id post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
JOIN post_actions pa ON pa.post_id = p.id AND
post_action_type_id = (
SELECT id FROM post_action_types WHERE name_key = 'like'
) AND
pa.user_id IN (
SELECT gu.user_id
FROM group_users gu
WHERE gu.group_id = ( SELECT id FROM groups WHERE name ilike 'trust_level_3' )
)
WHERE category_id = (
SELECT id FROM categories WHERE name ilike 'reviews'
)
