Badge based on when user was promoted to moderator first time?

data & reporting
1

Hey guys! I tried search, but could not find anything.

Is there any way to assign a badge based on how long a user has been a moderator?

In our case, we have mods who have been an essential part of the team for many years and we want to give them a special badge.

Basically the query should check conditions:
Is user currently a mod?
How many days has user held a moderator role?

That way we want to give out badges for modes who have been active 1y, 3y, 5y etc

2

Haven’t tried setting up as badge sql, but here is a data explorer query for getting the moderator days as their role:

WITH moderator_role_dates AS (
    SELECT
        gu.user_id,
        MIN(gu.created_at) AS role_granted_date
    FROM
        group_users gu
        JOIN groups g ON gu.group_id = g.id
    WHERE
        g.name = 'moderators'
    GROUP BY
        gu.user_id
),
current_date_info AS (
    SELECT
        CURRENT_DATE AS today
)
SELECT
    mrd.user_id,
    u.username,
    mrd.role_granted_date,
    cdi.today,
    (cdi.today - mrd.role_granted_date) AS days_in_role
FROM
    moderator_role_dates mrd
    JOIN users u ON mrd.user_id = u.id,
    current_date_info cdi
ORDER BY
    days_in_role DESC
3 Likes
7

I’ve just tagged this as sql-triggered-badge , but thought I’d check as if you’re looking for an automatic one or not? I’m assuming you may not have a huge amount of mods and could maybe grant this badge manually based on a data explorer query?

I think a simplified version of the one above could be:

-- [params]
-- int :years

WITH time_in_mod_group AS (

SELECT
    user_id,
    (CURRENT_DATE - created_at::date) AS days_as_mod
FROM group_users
WHERE group_id = 2 -- group id of the 'moderator' group
  AND user_id > 0 -- exclude system users and bots

)

SELECT
    user_id,
    days_as_mod
FROM time_in_mod_group
WHERE days_as_mod > (365 * :years)

This one would work as a data explorer query rather than a set of SQL badges, and can pick out a list of users who fit the criteria so you can manually grant them the badge.

There is one caveat that if they left the mod group at any point and rejoined then it would only count the latest inclusion. I think it would be possible to use the user_histories table to workaround that, but if it’s an exception then it’s likely easier to account for that manually as well.

1 Like
8

Yep, supposed to be automatic. Basically, we just want to set multiple badges for moderators (1 year, 2 years, 3 years etc) based on how long they have been a mod and grant them badges automatically.

1 Like
9

I think the one above is adaptable if you remove the parameter and change the WHERE to be the different day thresholds for each, eg.

WHERE days_as_mod > 365

(The trigger would be update daily)

10

Thx, technically we could do that, but as long as we can do it automatically in a way that doesn’t compromise security, we will rather opt for that. :slight_smile:

I really appreciate the code and we will try and implement it together with our hoster CommuniteQ and report back here

1 Like