用户首次晋升版主时发放徽章？

社区 数据与报告
2

尚未尝试将其设置为徽章 SQL，但这是一个用于获取版主作为其角色的天数的数据浏览器查询：

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