Cómo obtener el número de menciones que los grupos han tenido en el último año, y el número de miembros

Comunidad Datos e informes
,
1

Hola, soy moderador en el foro de Hopscotch y me preguntaba si podríamos pedir ayuda con estas consultas de Data Explorer:

  • Cuántas menciones @ han tenido los grupos en los últimos 365 días
    • ordenadas de menor a mayor,
    • y solo si el grupo existe desde hace más de 365 días (es decir, la fecha de creación del grupo fue hace más de 365 días)
  • Cuántas menciones @ han tenido los grupos en los últimos 365 días
    • solo si el grupo tiene menos de 5 menciones en los últimos 365 días
    • ordenadas de menor a mayor,
    • y solo si el grupo existe desde hace más de 365 días (es decir, la fecha de creación del grupo fue hace más de 365 días)
  • Cuántos miembros tienen los grupos,
    • solo si el grupo tiene menos de 10 miembros
    • ordenadas de menor a mayor

El objetivo es que trabajemos en la eliminación de grupos inactivos. (Yo pasaría las consultas a un administrador para que las cree).

Gracias de antemano,
Tracey

(Agregaría la etiqueta data-explorer, pero parece que no puedo hacerlo en este momento)

1 me gusta
2

Hola,

Solo para aclarar, ¿necesitas tres consultas?

  1. Para todos los grupos con más de 365 días de antigüedad, listar cuántas menciones han tenido, en orden ascendente.
  2. Para todos los grupos con más de 365 días de antigüedad, listar cuántas menciones han tenido, en orden ascendente, solo si han tenido menos de cinco menciones.
  3. Para todos los grupos con menos de 10 miembros, listar cuántos miembros tienen.

Si es así, puedo hacerlo por ti; toda la información que necesitas estaría almacenada en groups, group_users y group_mentions.

3

Por lo que puedo ver, Discourse no lleva un registro explícito de las menciones, pero sí rastrea los enlaces que se agregan a los temas. Una mención de grupo en un post se registra como el valor del campo url en la tabla topic_links, con el siguiente formato: "/groups/<nombre_del_grupo>". La siguiente consulta debería acercarse a lo que buscas. Necesitarás proporcionar valores para los parámetros start_date y end_date. Esas fechas deben estar en formato aaaa-mm-dd. Por ejemplo, para obtener datos del último año, ingresa 2020-01-01 como start_date y 2020-12-31 como end_date:

--[params]
-- date :start_date
-- date :end_date

WITH group_mentions AS (
SELECT
split_part(url, '/', 3) AS group_name
FROM topic_links tl
JOIN topics t ON t.id = tl.topic_id
WHERE internal = true
AND url LIKE '/groups/%'
AND t.deleted_at IS NULL
AND t.archetype = 'regular'
AND tl.created_at::date BETWEEN :start_date AND :end_date
)

SELECT
gm.group_name,
COUNT(gm.group_name) AS mention_count
FROM group_mentions gm
JOIN groups g ON g.name = gm.group_name
WHERE g.created_at::date <= :start_date
GROUP BY gm.group_name
ORDER BY mention_count DESC

La consulta solo devuelve menciones agregadas a publicaciones regulares (aunque sí incluye menciones agregadas a publicaciones tipo susurro). Si deseas incluir en los resultados las menciones agregadas a mensajes personales, elimina la línea AND t.archetype = 'regular' de la primera consulta.

Házmelo saber si esto no devuelve el tipo de datos que buscas. Intentaré responder a tus otras preguntas mañana si nadie lo hace antes.

3 Me gusta