# Insignia basada en cuándo fue la primera promoción de usuario a moderador?

**URL:** <https://meta.discourse.org/t/badge-based-on-when-user-was-promoted-to-moderator-first-time/303035>\
**Category:** Data & reporting\
**Tags:** sql-triggered-badge\
**Created:** [9 Abril, 2024 13:24 UTC](https://meta.discourse.org/t/badge-based-on-when-user-was-promoted-to-moderator-first-time/303035 "2024-04-09T13:24:34Z")\
**Posts on this page:** 1\
**Showing post:** 2

<div class="post-metadata">

**Author:** ![Lilly](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/lilly/32/575047_2.png) [@Lilly](https://meta.discourse.org/u/Lilly)\
**Post date:** [9 Abril, 2024 13:55 UTC](https://meta.discourse.org/t/badge-based-on-when-user-was-promoted-to-moderator-first-time/303035/2 "2024-04-09T13:55:02Z")

</div>

Aún no he intentado configurarlo como insignia SQL, pero aquí tienes una consulta del explorador de datos para obtener los días de moderador como su rol:

```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

```

---

_[View the full topic](https://meta.discourse.org/t/badge-based-on-when-user-was-promoted-to-moderator-first-time/303035)._