지난 1년간 그룹의 멘션 수와 멤버 수를 확인하는 방법

커뮤니티 구축 데이터 및 보고
,
1

안녕하세요, Hopscotch 포럼의 모더레이터입니다. 다음 Data Explorer 쿼리에 대해 도움을 요청할 수 있을까요?

  • 지난 365일 동안 그룹이 받은 @멘션 수
    • 가장 적은 순서부터 정렬
    • 그룹이 365일 이상 존재한 경우에만 (즉, 그룹 생성일이 365일 이상 이전인 경우)
  • 지난 365일 동안 그룹이 받은 @멘션 수
    • 지난 365일 동안 멘션이 5개 미만인 그룹만 포함
    • 가장 적은 순서부터 정렬
    • 그룹이 365일 이상 존재한 경우에만 (즉, 그룹 생성일이 365일 이상 이전인 경우)
  • 그룹의 멤버 수
    • 멤버가 10명 미만인 그룹만 포함
    • 가장 적은 순서부터 정렬

목표는 비활성 그룹을 정리하는 작업을 진행하는 것입니다. (쿼리를 관리자분께 전달하여 생성 요청할 예정입니다)

미리 감사드립니다.
Tracey

(data-explorer 태그를 추가하고 싶지만, 현재 제가 그 권한이 없는 것 같습니다)

2

안녕하세요,

확인차 문의드립니다. 총 3개의 쿼리가 필요한가요?

  1. 365일 이상 된 모든 그룹에 대해 언급(mention) 횟수를 오름차순으로 나열
  2. 365일 이상 된 모든 그룹에 대해 언급 횟수가 5회 미만인 경우에만, 언급 횟수를 오름차순으로 나열
  3. 멤버가 10명 미만인 모든 그룹에 대해 멤버 수를 나열

위와 같다면 제가 진행해 드릴 수 있습니다. 필요한 모든 정보는 groups, group_users, group_mentions 테이블에 저장되어 있습니다.

3

제가 파악한 바로는, Discourse는 멘션을 명시적으로 추적하지는 않지만, 주제에 추가된 링크는 추적합니다. 게시물 내의 그룹 멘션은 topic_links 테이블의 url 값으로 다음 형식으로 기록됩니다: "/groups/<group_name>". 다음 쿼리는 찾으시는 것과 거의 일치하는 결과를 제공해야 합니다. start_date와 end_date 매개변수에 값을 입력해야 합니다. 해당 날짜는 yyyy-mm-dd 형식이어야 합니다. 예를 들어, 지난 1년의 데이터를 가져오려면 start_date에 2020-01-01을, end_date에 2020-12-31을 입력하십시오:

--[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' 줄을 제거하십시오.

이 결과가 찾으시는 유형의 데이터가 아니라면 알려주세요. 그 전에 다른 사람이 답변하지 않는다면 내일 나머지 질문들을 처리해 보겠습니다.