Как узнать количество упоминаний групп за последний год и число участников

Создание сообщества Данные и отчетность
,
1

Привет! Я модератор на форуме Hopscotch и хотел бы попросить помощи с запросами для Data Explorer:

  • Сколько раз группы упоминались через @ за последние 365 дней
    • отсортировано от наименьшего к наибольшему,
    • и только если группа существует более 365 дней (то есть дата создания группы была более 365 дней назад)
  • Сколько раз группы упоминались через @ за последние 365 дней
    • только если у группы менее 5 упоминаний за последние 365 дней
    • отсортировано от наименьшего к наибольшему,
    • и только если группа существует более 365 дней (то есть дата создания группы была более 365 дней назад)
  • Сколько участников у групп,
    • только если у группы менее 10 участников
    • отсортировано от наименьшего к наибольшему

Наша цель — последовательно удалить неактивные группы. (Я передам эти запросы администратору для создания).

Заранее спасибо,
Трейси

(Я хотел бы добавить тег data-explorer, но, похоже, в данный момент у меня нет такой возможности)

2

Здравствуйте,

Просто уточню: вам нужны три запроса?

  1. Для всех групп, которым больше 365 дней, вывести количество упоминаний в порядке возрастания.
  2. Для всех групп, которым больше 365 дней, вывести количество упоминаний в порядке возрастания, но только если их меньше пяти.
  3. Для всех групп с менее чем 10 участниками вывести количество участников.

Если так, я могу это сделать для вас — вся необходимая информация хранится в таблицах groups, group_users и group_mentions.

3

Насколько я могу судить, Discourse явно не отслеживает упоминания, но он ведёт учёт ссылок, добавляемых к темам. Упоминание группы в сообщении записывается как значение поля url в таблице topic_links в следующем формате: "/groups/<group_name>". Приведённый ниже запрос должен дать вам результат, близкий к тому, что вы ищете. Вам нужно будет указать значения для параметров start_date и end_date. Эти даты должны быть в формате yyyy-mm-dd. Например, чтобы получить данные за последний год, введите 2020-01-01 как start_date и 2020-12-31 как 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

Запрос возвращает только упоминания, добавленные в обычные сообщения (хотя он включает и упоминания в приватных сообщениях типа «whisper»). Если вы хотите включить в результаты упоминания, добавленные в личные сообщения, удалите строку AND t.archetype = 'regular' из первого запроса.

Дайте знать, если этот запрос не возвращает нужные вам данные. Если до завтра на ваши другие вопросы никто не ответит, я постараюсь заняться ими.