Las consultas de insignias predeterminadas

[JammyDodger]

Esta es una guía de referencia para las consultas SQL de las insignias predeterminadas y su información de activación (cuando está disponible).

Insignias Principales

Aniversario

(Esta tiene algo de magia adicional en el backend para elegir las fechas, pero la incluiré de todos modos)

 start_date = start_date.iso8601(6)
    end_date = end_date.iso8601(6)

      SELECT u.id
        FROM users AS u
        JOIN posts AS p ON p.user_id = u.id
        JOIN topics AS t ON p.topic_id = t.id
       WHERE u.id > 0
         AND u.active
         AND NOT u.staged
         AND (u.silenced_till IS NULL OR u.silenced_till < '#{start_date}')
         AND (u.suspended_till IS NULL OR u.suspended_till < '#{start_date}')
         AND u.created_at <= '#{start_date}'
         AND NOT p.hidden
         AND p.deleted_at IS NULL
         AND p.created_at BETWEEN '#{start_date}' AND '#{end_date}'
         AND t.visible
         AND t.archetype <> 'private_message'
         AND t.deleted_at IS NULL
         AND NOT EXISTS (SELECT 1 FROM user_badges AS ub WHERE ub.user_id = u.id AND ub.badge_id = #{Badge::Anniversary} AND ub.granted_at BETWEEN '#{start_date}' AND '#{end_date}')
         AND NOT EXISTS (SELECT 1 FROM anonymous_users AS au WHERE au.user_id = u.id)
       GROUP BY u.id
      HAVING COUNT(p.id) > 0

Ejecutar consulta de revocación diariamente
La consulta apunta a publicaciones
Activador	Actualizar diariamente

Apreciado (número de Me gusta en varias publicaciones)

Las insignias Apreciado, Respetado y Admirado siguen el mismo patrón, pero con diferentes valores para p.like_count y HAVING COUNT(*).

SELECT p.user_id, CURRENT_TIMESTAMP AS granted_at
      FROM posts AS p
      WHERE p.like_count >= #{like_count}
        AND (:backfill OR p.user_id IN (:user_ids))
      GROUP BY p.user_id
      HAVING COUNT(*) > #{post_count}

Ejecutar consulta de revocación diariamente
La consulta apunta a publicaciones
Activador	Actualizar diariamente

Autobiógrafo

SELECT u.id user_id, CURRENT_TIMESTAMP granted_at
    FROM users u
    JOIN user_profiles up on u.id = up.user_id
    WHERE bio_raw IS NOT NULL AND LENGTH(TRIM(bio_raw)) > 10 AND
          uploaded_avatar_id IS NOT NULL AND
          (:backfill OR u.id IN (:user_ids) )

Ejecutar consulta de revocación diariamente
La consulta apunta a publicaciones
Activador	Cuando un usuario se edita o crea

Básico (Niveles de Confianza)

Las insignias Básico, Miembro, Regular y Líder siguen el mismo patrón, pero con un valor diferente de trust_level.

SELECT u.id user_id, current_timestamp granted_at FROM users u
      WHERE trust_level >= #{level.to_i} AND (
        :backfill OR u.id IN (:user_ids)
      )

Ejecutar consulta de revocación diariamente
La consulta apunta a publicaciones
Activador	Cuando un usuario cambia su nivel de confianza

Certificado y Licenciado

Estas no tienen consulta SQL. Son parte del Bot de Narrativa de Discourse y se otorgan programáticamente cuando un usuario completa los tutoriales interactivos (Discobot).

Editor

 SELECT p.user_id, min(p.id) post_id, min(p.created_at) granted_at
    FROM badge_posts p
    WHERE p.self_edits > 0 AND
        (:backfill OR p.id IN (:post_ids) )
    GROUP BY p.user_id

Ejecutar consulta de revocación diariamente
La consulta apunta a publicaciones
Activador	Cuando un usuario edita o crea una publicación

Entusiasta

Las insignias Entusiasta, Aficionado y Devoto siguen el mismo patrón, pero con un umbral diferente de HAVING COUNT(*).

WITH consecutive_visits AS (
        SELECT user_id
             , visited_at
             , visited_at - (DENSE_RANK() OVER (PARTITION BY user_id ORDER BY visited_at))::int s
          FROM user_visits
      ), visits AS (
        SELECT user_id
             , MIN(visited_at) "start"
             , DENSE_RANK() OVER (PARTITION BY user_id ORDER BY s) "rank"
          FROM consecutive_visits
      GROUP BY user_id, s
        HAVING COUNT(*) >= #{days}
      )
      SELECT user_id
           , "start" + interval '#{days} days' "granted_at"
        FROM visits
       WHERE "rank" = 1

Ejecutar consulta de revocación diariamente
La consulta apunta a publicaciones
Activador	Actualizar diariamente

Primer Emoji

No hay consulta SQL: la insignia se otorga en el momento en que se procesa una publicación que cumple los requisitos mediante CookedPostProcessor#grant_badges.

Criterio: La publicación procesada debe contener al menos un elemento img.emoji que no esté dentro de un bloque aside.quote. En otras palabras, los emojis escritos directamente en el cuerpo de la publicación cuentan; los emojis que aparecen solo dentro de un pasaje citado no cuentan.

Ejecutar consulta de revocación diariamente
La consulta apunta a publicaciones
Activador	Cocción de publicación (CookedPostProcessor)

Primera Bandera

SELECT pa1.user_id, pa1.created_at granted_at, pa1.post_id
    FROM (
      SELECT pa.user_id, MIN(pa.id) id
      FROM post_actions pa
      JOIN badge_posts p on p.id = pa.post_id
      WHERE post_action_type_id IN (
        SELECT f.id
        FROM flags f
        WHERE name != 'like'
        AND score_type IS FALSE
        AND require_message IS FALSE
      )
      AND (:backfill OR pa.post_id IN (:post_ids))
      GROUP BY pa.user_id
    ) x
    JOIN post_actions pa1 on pa1.id = x.id

Ejecutar consulta de revocación diariamente
La consulta apunta a publicaciones
Activador	Cuando un usuario actúa sobre una publicación

Primer Me Gusta

SELECT pa1.user_id, pa1.created_at granted_at, pa1.post_id
    FROM (
      SELECT pa.user_id, MIN(pa.id) id
      FROM post_actions pa
      JOIN badge_posts p on p.id = pa.post_id
      WHERE post_action_type_id = 2 AND
        (:backfill OR pa.post_id IN (:post_ids) )
      GROUP BY pa.user_id
    ) x
    JOIN post_actions pa1 on pa1.id = x.id

Ejecutar consulta de revocación diariamente
La consulta apunta a publicaciones
Activador	Cuando un usuario actúa sobre una publicación

Primer Enlace

SELECT l.user_id, l.post_id, l.created_at granted_at
    FROM
    (
      SELECT MIN(l1.id) id
      FROM topic_links l1
      JOIN badge_posts p1 ON p1.id = l1.post_id
      JOIN badge_posts p2 ON p2.id = l1.link_post_id
      WHERE NOT reflection AND p1.topic_id <> p2.topic_id AND not quote AND
        (:backfill OR ( p1.id in (:post_ids) ))
      GROUP BY l1.user_id
    ) ids
    JOIN topic_links l ON l.id = ids.id

Ejecutar consulta de revocación diariamente
La consulta apunta a publicaciones
Activador	Cuando un usuario edita o crea una publicación

Primera Mención

SELECT acting_user_id AS user_id, MIN(target_post_id) AS post_id, MIN(p.created_at) AS granted_at
    FROM user_actions
    JOIN posts p ON p.id = target_post_id
    JOIN topics t ON t.id = topic_id
    JOIN categories c on c.id = category_id
    WHERE action_type = 7
      AND NOT read_restricted
      AND p.deleted_at IS  NULL
      AND t.deleted_at IS  NULL
      AND t.visible
      AND t.archetype <> 'private_message'
      AND (:backfill OR p.id IN (:post_ids))
    GROUP BY acting_user_id

Ejecutar consulta de revocación diariamente
La consulta apunta a publicaciones
Activador	Cuando un usuario edita o crea una publicación

Primer Onebox

No hay consulta SQL. Se otorga durante la cocción de la publicación mediante CookedPostProcessor#grant_badges.

Criterio: La publicación procesada debe haber generado al menos un onebox: una vista previa de enlace enriquecida generada cuando una URL desnuda en su propia línea se resuelve en un recurso incrustable.

Ejecutar consulta de revocación diariamente
La consulta apunta a publicaciones
Activador	Cocción de publicación (CookedPostProcessor)

Primera Cita

SELECT ids.user_id, q.post_id, p3.created_at granted_at
    FROM
    (
      SELECT p1.user_id, MIN(q1.id) id
      FROM quoted_posts q1
      JOIN badge_posts p1 ON p1.id = q1.post_id
      JOIN badge_posts p2 ON p2.id = q1.quoted_post_id
      WHERE (:backfill OR ( p1.id IN (:post_ids) ))
      GROUP BY p1.user_id
    ) ids
    JOIN quoted_posts q ON q.id = ids.id
    JOIN badge_posts p3 ON q.post_id = p3.id

Ejecutar consulta de revocación diariamente
La consulta apunta a publicaciones
Activador	Cuando un usuario edita o crea una publicación

Primera Respuesta por Correo Electrónico

No hay consulta SQL. Se otorga durante la cocción de la publicación mediante CookedPostProcessor#grant_badges.

Criterio: post.is_reply_by_email? debe ser true, es decir, la publicación se envió respondiendo a un correo electrónico de notificación de Discourse en lugar de a través de la interfaz web. Requiere que la función de respuesta por correo electrónico entrante esté habilitada en el sitio.

Ejecutar consulta de revocación diariamente
La consulta apunta a publicaciones
Activador	Cocción de publicación (CookedPostProcessor)

Sugerencia original de la comunidad 'Reply by email' badge - #3 by lrossouw

Primer Compartir

SELECT views.user_id, i2.post_id, i2.created_at granted_at
    FROM
    (
      SELECT i.user_id, MIN(i.id) i_id
      FROM incoming_links i
      JOIN badge_posts p on p.id = i.post_id
      JOIN users u on u.id = i.user_id
      GROUP BY i.user_id
    ) as views
    JOIN incoming_links i2 ON i2.id = views.i_id

Ejecutar consulta de revocación diariamente
La consulta apunta a publicaciones
Activador	Actualizar diariamente

Nuevo Usuario del Mes

No hay una consulta SQL registrada contra la insignia. Se otorga mediante un trabajo programado diariamente (Jobs::GrantNewUserOfTheMonthBadges). El trabajo se ejecuta todos los días, pero solo otorga insignias por el mes calendario anterior, y solo una vez al mes.

Elegibilidad: El candidato debe haber creado su cuenta durante el mes calendario anterior, estar activo y no ser un usuario en etapa, no ser administrador ni moderador, y no estar suspendido. También debe haber publicado en al menos 2 temas distintos con al menos 2 publicaciones en total, y haber recibido al menos 2 me gusta.

Puntuación: Los candidatos se clasifican según una puntuación ponderada por me gusta. Cada me gusta recibido se pondera según el nivel de confianza de la persona que lo otorgó:

Quien da el me gusta	Peso
Administrador o Moderador	3.0
Nivel de Confianza 4	2.0
Nivel de Confianza 3	1.5
Nivel de Confianza 2	1.0
Nivel de Confianza 1	0.25
Nivel de Confianza 0	0.1

La puntuación final es SUM(me gusta ponderados) / (5 + número de publicaciones); dividir por el número de publicaciones atenúa la ventaja de los usuarios que publican mucho. Hasta 2 usuarios pueden recibir la insignia por mes. Cada ganador también recibe un mensaje del sistema.

Ejecutar consulta de revocación diariamente
La consulta apunta a publicaciones
Activador	Trabajo programado diariamente (otorga por el mes calendario anterior)

Buena Respuesta (Me gusta en una publicación)

Las insignias Buena Respuesta, Buena Respuesta y Gran Respuesta siguen el mismo patrón, pero con un umbral diferente para p.like_count.

SELECT p.user_id, p.id post_id, CURRENT_TIMESTAMP granted_at
      FROM badge_posts p
      WHERE p.post_number > 1 AND p.like_count >= #{count.to_i} AND
        (:backfill OR p.id IN (:post_ids) )

Ejecutar consulta de revocación diariamente
La consulta apunta a publicaciones
Activador	Cuando un usuario actúa sobre una publicación

Buen Compartir (Compartir enlaces)

Las insignias Buen Compartir, Buen Compartir y Gran Compartir siguen el mismo patrón, pero con un umbral diferente de HAVING COUNT(*).

SELECT views.user_id, i2.post_id, CURRENT_TIMESTAMP granted_at
      FROM
      (
        SELECT i.user_id, MIN(i.id) i_id
        FROM incoming_links i
        JOIN badge_posts p on p.id = i.post_id
        JOIN users u on u.id = i.user_id
        GROUP BY i.user_id,i.post_id
        HAVING COUNT(DISTINCT(i.ip_address, i.current_user_id)) >= #{count}
      ) as views
      JOIN incoming_links i2 ON i2.id = views.i_id

Ejecutar consulta de revocación diariamente
La consulta apunta a publicaciones
Activador	Actualizar diariamente

Buen Tema (Me gusta en un tema)

Las insignias Buen Tema, Buen Tema y Gran Tema siguen el mismo patrón, pero con un umbral diferente para p.like_count.

SELECT p.user_id, p.id post_id, CURRENT_TIMESTAMP granted_at
      FROM badge_posts p
      WHERE p.post_number = 1 AND p.like_count >= #{count.to_i} AND
        (:backfill OR p.id IN (:post_ids) )

Ejecutar consulta de revocación diariamente
La consulta apunta a publicaciones
Activador	Cuando un usuario actúa sobre una publicación

Por Amor (Máximo de Me gusta en un día)

Las insignias Por Amor, Amor Superior y Locamente Enamorado siguen el mismo patrón, pero con un umbral diferente de HAVING COUNT(*).

SELECT gdl.user_id, CURRENT_TIMESTAMP AS granted_at
      FROM given_daily_likes AS gdl
      WHERE gdl.limit_reached
        AND (:backfill OR gdl.user_id IN (:user_ids))
      GROUP BY gdl.user_id
      HAVING COUNT(*) >= #{count}

Ejecutar consulta de revocación diariamente
La consulta apunta a publicaciones
Activador	Actualizar diariamente

Enlace Popular (Clics en enlaces)

Las insignias Enlace Popular, Enlace Caliente y Enlace Famoso siguen el mismo patrón, pero con un umbral diferente de tl.clicks.

SELECT tl.user_id, post_id, CURRENT_TIMESTAMP granted_at
        FROM topic_links tl
        JOIN badge_posts p ON p.id = post_id
       WHERE NOT tl.internal
         AND tl.clicks >= #{count}
      GROUP BY tl.user_id, tl.post_id

Ejecutar consulta de revocación diariamente
La consulta apunta a publicaciones
Activador	Actualizar diariamente

Promotor (Invitaciones)

Las insignias Promotor, Campañero y Campeón siguen el mismo patrón, pero con un valor diferente de Nivel de Confianza necesario para los invitados y un umbral diferente de HAVING COUNT(*).

SELECT u.id user_id, CURRENT_TIMESTAMP granted_at
      FROM users u
      WHERE u.id IN (
        SELECT invited_by_id
        FROM invites i
        JOIN invited_users iu ON iu.invite_id = i.id
        JOIN users u2 ON u2.id = iu.user_id
        WHERE i.deleted_at IS NULL
        AND i.invited_by_id <> u2.id
        AND u2.active
        AND u2.trust_level >= #{trust_level.to_i}
        AND u2.silenced_till IS NULL
        GROUP BY invited_by_id
        HAVING COUNT(*) >= #{count.to_i}
      ) AND u.active AND u.silenced_till IS NULL AND u.id > 0 AND
      (:backfill OR u.id IN (:user_ids) )

Ejecutar consulta de revocación diariamente
La consulta apunta a publicaciones
Activador	Actualizar diariamente

Leer Directrices

 SELECT user_id, read_faq granted_at
    FROM user_stats
    WHERE read_faq IS NOT NULL AND (user_id IN (:user_ids) OR :backfill)

Ejecutar consulta de revocación diariamente
La consulta apunta a publicaciones
Activador	Cuando un usuario se edita o crea

Lector

SELECT id user_id, CURRENT_TIMESTAMP granted_at
    FROM users
    WHERE id IN
    (
      SELECT pt.user_id
      FROM post_timings pt
      JOIN badge_posts b ON b.post_number = pt.post_number AND
                            b.topic_id = pt.topic_id
      JOIN topics t ON t.id = pt.topic_id
      LEFT JOIN user_badges ub ON ub.badge_id = 17 AND ub.user_id = pt.user_id
      WHERE ub.id IS NULL AND t.posts_count > 100
      GROUP BY pt.user_id, pt.topic_id, t.posts_count
      HAVING COUNT(*) >= t.posts_count

Ejecutar consulta de revocación diariamente
La consulta apunta a publicaciones
Activador

Gracias (Me gusta dados + Me gusta recibidos)

Las insignias Gracias, Devuelve y Empático siguen el mismo patrón, pero con valores diferentes para us.likes_given y HAVING COUNT(*).

SELECT us.user_id, CURRENT_TIMESTAMP granted_at
      FROM user_stats AS us
      INNER JOIN posts AS p ON p.user_id = us.user_id
      WHERE p.like_count > 0
        AND us.likes_given >= #{likes_given}
        AND (:backfill OR us.user_id IN (:user_ids))
      GROUP BY us.user_id, us.likes_given
      HAVING COUNT(*) > #{likes_received}

Ejecutar consulta de revocación diariamente
La consulta apunta a publicaciones
Activador	Actualizar diariamente

Bienvenido

SELECT p.user_id, MIN(post_id) post_id, MIN(pa.created_at) granted_at
    FROM post_actions pa
    JOIN badge_posts p on p.id = pa.post_id
    WHERE post_action_type_id = 2 AND
        (:backfill OR pa.post_id IN (:post_ids) )
    GROUP BY p.user_id

Ejecutar consulta de revocación diariamente
La consulta apunta a publicaciones
Activador	Cuando un usuario actúa sobre una publicación

Editor de Wiki

SELECT pr2.user_id, pr2.post_id, pr2.created_at granted_at
    FROM
    (
      SELECT MIN(pr.id) id
      FROM post_revisions pr
      JOIN badge_posts p on p.id = pr.post_id
      WHERE p.wiki
          AND NOT pr.hidden
          AND (:backfill OR p.id IN (:post_ids))
      GROUP BY pr.user_id
    ) as X
    JOIN post_revisions pr2 ON pr2.id = X.id

Ejecutar consulta de revocación diariamente
La consulta apunta a publicaciones
Activador	Cuando un usuario edita o crea una publicación

Reacciones de Discourse

Primera Reacción

SELECT user_id, created_at AS granted_at, post_id
  FROM (
           SELECT ru.post_id, ru.user_id, ru.created_at,
                  ROW_NUMBER() OVER (PARTITION BY ru.user_id ORDER BY ru.created_at) AS row_number
           FROM discourse_reactions_reaction_users ru
                JOIN badge_posts p ON ru.post_id = p.id
           WHERE :backfill
              OR ru.post_id IN (:post_ids)
       ) x
  WHERE row_number = 1

Ejecutar consulta de revocación diariamente
La consulta apunta a publicaciones
Activador	Cuando un usuario edita o crea una publicación

Discourse Solved

¡Resuelto!

 SELECT post_id, user_id, created_at AS granted_at
  FROM (
           SELECT p.id AS post_id, p.user_id, dsst.created_at,
              ROW_NUMBER() OVER (PARTITION BY p.user_id ORDER BY dsst.created_at) AS row_number
           FROM discourse_solved_solved_topics dsst
              JOIN badge_posts p ON dsst.answer_post_id = p.id
              JOIN topics t ON p.topic_id = t.id
           WHERE p.user_id <> t.user_id -- ignorar temas resueltos por el OP
              AND (:backfill OR p.id IN (:post_ids))
       ) x
  WHERE row_number = 1

Ejecutar consulta de revocación diariamente
La consulta apunta a publicaciones
Activador	Cuando un usuario edita o crea una publicación

Consejero de Orientación

Las insignias para Consejero de Orientación, Lo-sabe-todo y Institución de Soluciones siguen el mismo patrón, pero con un umbral diferente de HAVING COUNT (*) >=:

SELECT p.user_id, MAX(pcf.created_at) AS granted_at
   FROM post_custom_fields pcf
        JOIN badge_posts p ON pcf.post_id = p.id
        JOIN topics t ON p.topic_id = t.id
   WHERE pcf.name = 'is_accepted_answer'
     AND p.user_id <> t.user_id -- ignorar temas resueltos por el OP
     AND (:backfill OR p.id IN (:post_ids))
   GROUP BY p.user_id
   HAVING COUNT(*) >= #{min_count}

Ejecutar consulta de revocación diariamente
La consulta apunta a publicaciones
Activador	Cuando un usuario edita o crea una publicación

Discourse Github

No puedo leer estas.

app/lib/github_badges.rb

Fuentes:

lib/badge_queries.rb

db/fixtures/001_badges.rb

db/fixtures/001_badges.rb

app/lib/github_badges.rb

[Lilly (Lillian)]

Esto es genial @JammyDodger ¡gracias por hacer este útil tema!