Come ottenere il numero di menzioni dei gruppi nell'ultimo anno e il numero di membri

Comunità Dati e reportistica
,
1

Ciao, sono un moderatore del forum Hopscotch e mi chiedevo se potessimo chiedere aiuto con queste query di Data Explorer:

  • Quante menzioni @ hanno ricevuto i gruppi negli ultimi 365 giorni
    • ordinate dal più basso al più alto,
    • e solo se il gruppo esiste da più di 365 giorni (cioè la data di creazione del gruppo è di oltre 365 giorni fa)
  • Quante menzioni @ hanno ricevuto i gruppi negli ultimi 365 giorni
    • solo se il gruppo ha meno di 5 menzioni negli ultimi 365 giorni
    • ordinate dal più basso al più alto,
    • e solo se il gruppo esiste da più di 365 giorni (cioè la data di creazione del gruppo è di oltre 365 giorni fa)
  • Quanti membri hanno i gruppi,
    • solo se il gruppo ha meno di 10 membri
    • ordinati dal più basso al più alto

L’obiettivo è lavorare per rimuovere i gruppi inattivi. (Passerei le query a un amministratore per crearle)

Grazie in anticipo,
Tracey

(Aggungerei il tag data-explorer, ma sembra che al momento non possa farlo)

2

Ciao,

Solo per chiarire, ti servono tre query?

  1. Per tutti i gruppi con più di 365 giorni, elencare il numero di menzioni che hanno ricevuto, in ordine crescente
  2. Per tutti i gruppi con più di 365 giorni, elencare il numero di menzioni che hanno ricevuto, in ordine crescente, solo se hanno ricevuto meno di cinque menzioni
  3. Per tutti i gruppi con meno di 10 membri, elencare il numero di membri che hanno

Se è così, posso farlo per te: tutte le informazioni di cui hai bisogno sono memorizzate in groups, group_users e group_mentions.

3

Per quanto ne so, Discourse non tiene traccia esplicita delle menzioni, ma registra i link aggiunti ai topic. Una menzione di un gruppo in un post viene salvata come valore del campo url nella tabella topic_links con il seguente formato: "/groups/<nome_gruppo>". La seguente query dovrebbe restituire risultati molto vicini a quelli che cerchi. Dovrai fornire i valori per i parametri start_date e end_date. Le date devono essere nel formato aaaa-mm-gg. Ad esempio, per ottenere i dati relativi all’anno scorso, inserisci 2020-01-01 come start_date e 2020-12-31 come 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 query restituisce solo le menzioni aggiunte ai post regolari (include però anche quelle aggiunte ai post “whisper”). Se desideri includere nelle risposte anche le menzioni aggiunte ai messaggi privati, rimuovi la riga AND t.archetype = 'regular' dalla prima query.

Fammi sapere se questi dati non sono quelli che cercavi. Se nessuno risponde prima, proverò a rispondere alle tue altre domande domani.