The discussion revolves around creating a SQL badge query to reward members who successfully invite more than 25 people. iidbae is looking to create a badge similar to existing ones like Campaigner and Champion, but with a higher invitation threshold. schleifer provides guidance on how to modify the existing invite badge query by replacing variables with the desired number of people and trust level. iidbae confirms the correct query, and schleifer verifies that it will automatically grant a badge to members who have successfully invited 25 people when they reach trust level 2. The final query is:
SELECT u.id user_id, current_timestamp granted_at
FROM users u
WHERE u.id IN (
SELECT invited_by_id
FROM invites i
JOIN users u2 ON u2.id = i.user_id
WHERE i.deleted_at IS NULL AND u2.active AND u2.trust_level >= 2 AND u2.silenced_till IS NULL
GROUP BY invited_by_id
HAVING COUNT(*) >= 25
) AND u.active AND u.silenced_till IS NULL AND u.id > 0 AND
(:backfill OR u.id IN (:user_ids) )
So, I want to give a badge to a member who has successful invited 25 people.
Just like the existing badge, the Campaigner (3 people) or Champion (5 people).
The reward is only a badge without raising the Trust Level.
The concept is similar with the SQL Badge Query in “A badge for 10 accepted answers” Discourse Solved Only need changes the number.
Hi Andrew, thank you for your simple and easy explanation.
I am really appreciate that.
I just want to confirm if the below query is right:
SELECT u.id user_id, current_timestamp granted_at
FROM users u
WHERE u.id IN (
SELECT invited_by_id
FROM invites i
JOIN users u2 ON u2.id = i.user_id
WHERE i.deleted_at IS NULL AND u2.active AND u2.trust_level >= 2 AND u2.silenced_till IS NULL
GROUP BY invited_by_id
HAVING COUNT(*) >= 25
) AND u.active AND u.silenced_till IS NULL AND u.id > 0 AND
(:backfill OR u.id IN (:user_ids) )
So the query will automatically give a badge to a member who has successful invite 25 people when the TL is 2. Am I right?
