Admin dashboard report reference guide

Discourse · 9월 27, 2022, 8:25오후

이 가이드는 Admin Dashboard Reports의 작동 방식, 표시되는 데이터, 각 보고서에 해당하는 Data Explorer SQL 쿼리, 그리고 각 보고서의 Ruby 코드를 어디서 찾을 수 있는지를 설명하는 참고 자료입니다.

필요한 사용자 권한: Staff

Discourse에는 커뮤니티에 대한 통계를 탐색하는 데 유용한 여러 내장 Admin Dashboard Reports가 포함되어 있습니다. 이 보고서에 액세스하려면 사이트에서 discourse.example.com/admin/dashboard/reports를 방문하거나(또는 사이드바의 Reports 링크를 클릭)하십시오. 이 보고서에 대한 액세스 권한은 Staff 사용자만 가집니다.

사이트의 모든 사용자 데이터(관리 페이지 방문과 같은 Staff 활동 포함)가 이 보고서에 포함됩니다. 보고서에서 사용자에게 적용되는 유일한 조건은 ‘실제’ 사용자여야 한다는 것이며, 이는 system 사용자를 보고서에서 제외하는 데 사용됩니다.

플러그인은 add_report(name, &block)을 사용하여 대시보드에 보고서를 추가할 수도 있습니다.

대부분의 보고서에 대한 Ruby 모델은 discourse/app/models/concerns/reports/에 위치해 있습니다. 일부 보고서는 discourse/app/models/report.rb도 참조합니다.

dashboard-sql 토픽에는 Admin Dashboard Reports와 동일한 보고서를 생성할 수 있는 모든 해당 SQL 쿼리가 포함되어 있습니다. 이러한 쿼리는 Data Explorer 플러그인 내에서 및 Discourse API를 사용한 Data Explorer 쿼리 실행에 사용할 수 있습니다.

대시보드에서 특정 보고서를 숨기려면 dashboard_hidden_reports 사이트 설정을 사용하십시오.

Accepted solutions

해결책으로 표시된 게시물의 일일 집계.

Ruby 코드: discourse-solved/plugin.rb at main · discourse/discourse-solved · GitHub

SQL 쿼리: Dashboard Report - Accepted Solutions

Admin Logins

위치와 함께된 관리자 로그인 시간 목록.

Ruby 코드: discourse/app/models/concerns/reports/staff_logins.rb

SQL 쿼리: Dashboard Report - Admin Logins

Anonymous

계정에 로그인하지 않은 방문자의 새로운 페이지뷰 수.

Ruby 코드: discourse/app/models/concerns/reports/consolidated_page_views.rb

SQL 쿼리: Dashboard Report - Anonymous

Bookmarks

북마크된 새로운 토픽 및 게시물 수.

Ruby 코드: discourse/app/models/concerns/reports/bookmarks.rb

SQL 쿼리: Dashboard Report - Bookmarks

Consolidated API Requests

날짜별 API 사용 통계로, 일반 API 요청과 사용자 API 요청을 모두 추적합니다.

Ruby 코드: discourse/app/models/concerns/reports/consolidated_api_requests.rb at main · discourse/discourse · GitHub

SQL 쿼리: Dashboard Report - Consolidated API Requests

Consolidated Pageviews

로그인된 사용자,匿名用户 및 크롤러의 페이지뷰. 이 보고서는 Site Traffic 보고서에 의해 대체된 레거시 보고서입니다.

Ruby 코드: discourse/app/models/concerns/reports/consolidated_page_views.rb

SQL 쿼리: Dashboard Report - Consolidated Pageviews

Consolidated Pageviews with Browser Detection (Deprecated)

로그인된 사용자,匿名用户, 알려진 크롤러 및 기타의 페이지뷰. 이 보고서는 비활성화되었으며 이제 Site Traffic 보고서에 위임됩니다.

Ruby 코드: discourse/app/models/concerns/reports/consolidated_page_views_browser_detection.rb

SQL 쿼리: Dashboard Report - Consolidated Pageviews with Browser Detection

DAU/MAU

지난 1일 동안 로그인한 멤버 수를 지난 1개월 동안 로그인한 멤버 수로 나눈 값 – 커뮤니티의 '중속성(stickiness)'을 나타내는 %를 반환합니다. 20% 이상을 목표로 하십시오.

Ruby 코드: discourse/app/models/concerns/reports/dau_by_mau.rb

SQL 쿼리: Dashboard Report - DAU/MAU

Daily Engaged Users

지난 1일 동안 '좋아요’를 누르거나 게시물을 작성한 사용자 수.

Ruby 코드: discourse/app/models/concerns/reports/daily_engaged_users.rb

SQL 쿼리: Dashboard Report - Daily Engaged Users

Emails Sent

보낸 새로운 이메일 수.

Ruby 코드: discourse/app/models/concerns/reports/emails.rb

SQL 쿼리: Dashboard Report - Emails Sent

Flags

새로운 플래그 수.

Ruby 코드: discourse/app/models/concerns/reports/flags.rb

SQL 쿼리: Dashboard Report - Flags

Flags Status

플래그 유형, 게시자, 플래그를 등록한 사람, 해결까지 걸린 시간을 포함한 플래그 상태 목록.

Ruby 코드: discourse/app/models/concerns/reports/flags_status.rb

SQL 쿼리: Dashboard Report - Flags Status

Likes

새로운 ‘좋아요’ 수.

Ruby 코드: discourse/app/models/concerns/reports/likes.rb

SQL 쿼리: Dashboard Report - Likes

Logged In

로그인된 사용자의 새로운 페이지뷰 수.

Ruby 코드: discourse/app/controllers/admin/reports_controller.rb#L5

SQL 쿼리: Dashboard Report - Logged In

Moderator Activity

검토된 플래그, 읽기 시간, 생성된 토픽, 생성된 게시물, 생성된 개인 메시지, 수정 사항을 포함한 모더레이터 활동 목록.

SQL 쿼리: Dashboard Report - Moderator Activity

Moderator Warning

모더레이터가 개인 메시지로 보낸 경고 수.

Ruby 코드: discourse/app/models/concerns/reports/moderator_warning_private_messages.rb

SQL 쿼리: Dashboard Report - Moderator Warnings

New Contributors

이 기간 동안 첫 번째 게시물을 작성한 사용자 수.

Ruby 코드: discourse/app/models/concerns/reports/new_contributors.rb

SQL 쿼리: Dashboard Report - New Contributors

Notify Moderators

플래그로 인해 모더레이터가 비공개로 통지를 받은 횟수.

Ruby 코드: discourse/app/models/concerns/reports/notify_moderators_private_messages.rb

SQL 쿼리: Dashboard Report - Notify Moderators

Notify User

플래그로 인해 사용자가 비공개로 통지를 받은 횟수.

Ruby 코드: discourse/app/models/concerns/reports/notify_user_private_messages.rb

SQL 쿼리: Dashboard Report - Notify User

Overall Sentiment

지정된 기간 동안 “Sentiment” AI로 긍정적 또는 부정적으로 분류된 게시물 수.

Ruby 코드: https://github.com/discourse/discourse-ai/blob/main/lib/sentiment/entry_point.rb

SQL 쿼리: Dashboard Report - Overall Sentiment

Pageviews

모든 방문자의 새로운 페이지뷰 수. Consolidated Pageviews의 합계와 동일합니다.

Discourse는 다음 쿼리를 사용하여 총 페이지뷰를 결정합니다:

SQL 쿼리: Dashboard Report - Consolidated Pageviews

Post Edits

새로운 게시물 편집 수.

Ruby 코드: discourse/app/models/concerns/reports/post_edits.rb

SQL 쿼리: Dashboard Report - Post Edits

Posts

선택된 시간 기간 동안 생성된 새로운 게시물

Ruby 코드: discourse/app/models/concerns/reports/posts.rb

SQL 쿼리: Dashboard Report - Posts

Post Emotion

다음 중 하나의 감정으로 AI에 의해 분류된 게시물 수: 슬픔, 놀람, 두려움, 분노, 기쁨, 혐오 - 지정된 기간 동안 게시자의 신뢰 수준별로 그룹화.

Ruby 코드: https://github.com/discourse/discourse-ai/blob/main/lib/sentiment/entry_point.rb

SQL 쿼리: Dashboard Report - Post Emotion

Reactions

최근 반응 목록.

Ruby 코드: discourse-reactions/plugin.rb at main · discourse/discourse-reactions · GitHub

SQL 쿼리: Dashboard Report - Reactions

Signups

이 기간의 새로운 계정 등록.

Ruby 코드: discourse/app/models/concerns/reports/signups.rb

SQL 쿼리: Dashboard Report - Signups

Site Traffic

로그인된 브라우저,匿名用户 브라우저, 크롤러 및 기타 트래픽의 페이지뷰. 이것은 레거시 Consolidated Pageviews 보고서를 대체하는 주요 트래픽 보고서입니다.

Ruby 코드: discourse/app/models/concerns/reports/site_traffic.rb

SQL 쿼리: Dashboard Report - Site Traffic

Suspicious Logins

이전 로그인과 의심스럽게 다른 새로운 로그인에 대한 세부 정보.

Ruby 코드: discourse/app/models/concerns/reports/suspicious_logins.rb

SQL 쿼리: Dashboard Report - Suspicious Logins

System

System이 자동으로 보낸 개인 메시지 수.

Ruby 코드: discourse/app/models/concerns/reports/system_private_messages.rb

SQL 쿼리: Dashboard Report - System

Time to first response

새로운 토픽에 대한 첫 번째 응답의 평균 시간(시간 단위).

Ruby 코드: discourse/app/models/concerns/reports/time_to_first_response.rb + discourse/discourse/blob/main/app/models/topic.rb#L1799-L1844

SQL 쿼리: Dashboard Report - Time to First Response

Top Ignored / Muted Users

많은 다른 사용자들에게 음소거되고/또는 무시된 사용자.

Ruby 코드: discourse/app/models/concerns/reports/top_ignored_users.rb

SQL 쿼리: Dashboard Report - Top Ignored / Muted Users

Top Referrers

공유한 링크에 대한 클릭 수로 나열된 사용자.

Ruby 코드: discourse/app/models/concerns/reports/top_referrers.rb

SQL 쿼리: Dashboard Report - Top Referrers

Top Traffic Sources

이 사이트에 가장 많이 링크한 외부 출처.

Ruby 코드: discourse/app/models/concerns/reports/top_traffic_sources.rb

SQL 쿼리: Dashboard Report - Top Traffic Sources

Top Uploads

확장자, 파일 크기 및 작성자로 모든 업로드를 나열.

Ruby 코드: discourse/app/models/concerns/reports/top_uploads.rb

SQL 쿼리: Dashboard Report - Top Uploads

Top Users by likes received

'좋아요’를 가장 많이 받은 상위 10명의 사용자.

Ruby 코드: discourse/app/models/concerns/reports/top_users_by_likes_received.rb

SQL 쿼리: Dashboard Report - Top Users by Likes Received

Top Users by likes received from a user with a lower trust level

더 높은 신뢰 수준의 상위 10명의 사용자에게 더 낮은 신뢰 수준의 사람들이 '좋아요’를 누른 경우.

Ruby 코드: discourse/app/models/concerns/reports/top_users_by_likes_received_from_inferior_trust_level.rb

SQL 쿼리: Dashboard Report - Top Users by Likes Received from a User with a Lower Trust Level

Top Users by likes received from a variety of people

다양한 사람들로부터 '좋아요’를 받은 상위 10명의 사용자.

Ruby 코드: discourse/app/models/concerns/reports/top_users_by_likes_received_from_a_variety_of_people.rb

SQL 쿼리: Dashboard Report - Top Users by Likes Received From a Variety of People

Topics

이 기간 동안 생성된 새로운 토픽.

Ruby 코드: discourse/app/models/concerns/reports/topics.rb

SQL 쿼리: Dashboard Report - Topics

Topics with no response

응답을 받지 못한 새로운 토픽 생성 수.

Ruby 코드: discourse/app/models/concerns/reports/topics_with_no_response.rb

SQL 쿼리: Dashboard Report - Topics with No Response

Topic View Stats

匿名用户 및 로그인된 사용자 분해와 함께 뷰 수 기준 상위 100개 토픽, 카테고리별로 필터링 가능.

Ruby 코드: discourse/app/models/concerns/reports/topic_view_stats.rb

SQL 쿼리: Dashboard Report - Topic View Stats

Trending Search Terms

클릭률과 함께 가장 인기 있는 검색어.

Ruby 코드: discourse/app/models/concerns/reports/trending_search.rb

SQL 쿼리: Dashboard Report - Trending Search Terms

Trust Level growth

이 기간 동안 신뢰 수준을 높인 사용자 수.

Trust Level Growth 보고서는 Discourse 데이터베이스의 user_histories 테이블에서 데이터를 가져옵니다. 구체적으로, 이 보고서는 사용자 신뢰 수준 증가에 대해 user_histories.action이 기록된 횟수를 계산합니다.

Ruby 코드: discourse/app/models/concerns/reports/trust_level_growth.rb

SQL 쿼리: Dashboard Report - Trust Level Growth

Unaccepted policies

이 대시보드 보고서는 특정 사용자에 의해 승인되지 않은 정책을 가진 토픽을 식별합니다.

Ruby 코드: discourse-policy/plugin.rb at main · discourse/discourse-policy · GitHub

SQL 쿼리: Dashboard Report - Unaccepted Policies

User Flagging Ratio

플래그에 대한 Staff 응답 비율(불일치에서 일치로)로 정렬된 사용자 목록.

Ruby 코드: discourse/app/models/concerns/reports/user_flagging_ratio.rb

SQL 쿼리: Dashboard Report - User Flagging Ratio

User notes

최근 사용자 메모 목록.

Ruby 코드: discourse-user-notes/plugin.rb at main · discourse/discourse-user-notes · GitHub

SQL 쿼리: Dashboard Report - User Notes

User Profile Views

사용자 프로필의 총 새로운 뷰.

Ruby 코드: discourse/app/models/concerns/reports/profile_views.rb

SQL 쿼리: Dashboard Report - User Profile Views

User Visits

선택된 시간 기간(오늘, 어제, 지난 7일 등) 동안 포럼의 로그인된 사용자 방문 총 수.

User Visit은 고유한 로그인된 사용자가 사이트를 방문할 때마다 최대 하루 1회까지 계산됩니다. 예를 들어, 사용자가 한 주 동안 매일 사이트를 방문했다면 Discourse는 이를 7개의 사용자 방문으로 계산합니다.

Ruby 코드: discourse/app/models/concerns/reports/visits.rb

SQL 쿼리: Dashboard Report - User Visits

User Visits (mobile)

모바일 장치를 사용하여 방문한 고유한 로그인된 사용자 수.

Ruby 코드: discourse/app/models/concerns/reports/mobile_visits.rb

SQL 쿼리: Dashboard Report - User Visits

User-to-User (excluding replies)

새로 시작된 개인 메시지 수.

Ruby 코드: discourse/app/models/concerns/reports/user_to_user_private_messages.rb

SQL 쿼리: Dashboard Report - User-to-User

User-to-User (with replies)

모든 새로운 개인 메시지와 응답의 수.

Ruby 코드: discourse/app/models/concerns/reports/user_to_user_private_messages_with_replies.rb

SQL 쿼리: Dashboard Report - User-to-User

Users per Trust Level

신뢰 수준별로 그룹화된 사용자 수.

Ruby 코드: discourse/app/models/concerns/reports/users_by_trust_level.rb

SQL 쿼리: Dashboard Report - Users Per Trust Level

Users per Type

관리자, 모더레이터, 정지, 음소거별로 그룹화된 사용자 수.

Ruby 코드: discourse/app/models/concerns/reports/users_by_type.rb

SQL 쿼리: Dashboard Report - Users Per Type

Web Crawler Pageviews

시간 경과에 따른 웹 크롤러의 총 페이지뷰.

Ruby 코드: discourse/app/models/report.rb

SQL 쿼리: Dashboard Report - Web Crawler Pageviews

Web Crawler User Agents

페이지뷰로 정렬된 웹 크롤러 사용자 에이전트 목록.

Ruby 코드: discourse/app/models/concerns/reports/web_crawlers.rb

SQL 쿼리: Dashboard Report - Web Crawler User Agents

pfaffman · 9월 28, 2022, 1:28오후

I don’t see a link to this on /admin. Am I failing to read? It would seem that this should be more discoverable. I think that I knew that these reports were here but looked and couldn’t find them.

Though it took me just a few minutes to find, it might be nice to add something like

maiki · 9월 28, 2022, 3:28오후

Yeah, might be nice in a PM to staff to mention them when a site is first created.

pfaffman · 9월 28, 2022, 3:33오후

Sorry. I sure thought I’d seen it somewhere before.

You just can’t get people to read stuff. . . .Except I could read the source code to find how to do it in a plugin?

But maybe update the above to

I think that might be what really threw me. (But no, I have no excuse.)

maiki · 9월 28, 2022, 3:40오후

I made the topic a wiki, go for it!

hello-smile6 · 5월 19, 2023, 1:06오전

That doesn’t match the UI (the UI uses 20%), which one should it be?

JammyDodger · 5월 19, 2023, 4:55오전

Nice catch. It was recently updated to 20%. I’ll make the change in the OP.

Yotam_Hagay · 11월 30, 2023, 2:53오후

Hi @SaraDev Can this report output be obtained with an SQL query? Can you share it?
Thanks

SaraDev · 11월 30, 2023, 8:19오후

Yes, you can use the following SQL report for Top Traffic Sources:

-- [params]
-- date :start_date = 01/05/2023
-- date :end_date = 03/06/2023

WITH count_links AS (
  
SELECT COUNT(*) AS clicks,
       ind.name AS domain
FROM incoming_links il
  INNER JOIN posts p ON p.deleted_at ISNULL AND p.id = il.post_id
  INNER JOIN topics t ON t.deleted_at ISNULL AND t.id = p.topic_id
  INNER JOIN incoming_referers ir ON ir.id = il.incoming_referer_id
  INNER JOIN incoming_domains ind ON ind.id = ir.incoming_domain_id
WHERE t.archetype = 'regular'
  AND il.created_at::date BETWEEN :start_date AND :end_date 
GROUP BY ind.name
ORDER BY clicks DESC
), 

count_topics AS (
  
SELECT COUNT(DISTINCT p.topic_id) AS topics,
       ind.name AS domain
FROM incoming_links il
INNER JOIN posts p ON p.deleted_at ISNULL AND p.id = il.post_id
INNER JOIN topics t ON t.deleted_at ISNULL AND t.id = p.topic_id
INNER JOIN incoming_referers ir ON ir.id = il.incoming_referer_id
INNER JOIN incoming_domains ind ON ind.id = ir.incoming_domain_id
WHERE t.archetype = 'regular'
  AND il.created_at > (CURRENT_TIMESTAMP - INTERVAL '30 DAYS')
GROUP BY ind.name
) 

SELECT cl.domain, 
       cl.clicks AS "Clicks", 
       ct.topics AS "Topics"
FROM count_links cl
JOIN count_topics ct ON cl.domain = ct.domain
LIMIT 10

With this query note that the date parameters accepts dates in the format of day/month/year.

Yotam_Hagay · 12월 2, 2023, 10:49오후

Hi @SaraDev, thank you for sharing the query.
A more general question reg this report, and the incoming_links table actually - it only represents traffic for posts pages and not the traffic to all the forum pages, right?

Context: I’m looking to analyze trends in the forum overall traffic and I was hoping to get the overall traffic by sources from the Top Traffic Sources report.
But while the overall traffic is ~ 272K in the last month (users and anons), the total clicks in the traffic sources report for the same timeframe is only 59K.
Also, I see that you use inner join with the topics and posts tables which means that if there is no post_id attached to the click you don’t count it.

Can you please confirm my conclusion and perhaps explain a bit about the logic behind the incoming_links table?

Yotam_Hagay · 12월 3, 2023, 1:53오후

Hi @SaraDev I ran this query and the result does not precisely match the Posts report in the general tab.
E.G for Nov 30th:
Query = 112 posts
Report = 120 posts
Can you please check the gap?
Thanks

JammyDodger · 12월 3, 2023, 3:48오후

Just an FYI @Yotam_Hagay - even though Sara’s the OP, the guide is the responsibility of everyone There’s no need for an @mention on each post.

Yotam_Hagay · 12월 5, 2023, 6:19오전

Thank you @JammyDodger for clarifying.
Is there anyone else I can tag or turn to to get an answer?

Yotam_Hagay · 12월 5, 2023, 8:29오전

Sara Devlaeminck:

SQL Time to first response

--[params]
-- date :date_start
-- date :date_end

WITH first_reply AS (
    SELECT 
        p.topic_id, 
        MIN(post_number) post_number, 
        t.created_at
    FROM posts p
    INNER JOIN topics t ON (p.topic_id = t.id)
    WHERE p.deleted_at IS NULL
        AND p.post_number != 1
        AND p.post_type = 1
        AND p.user_id > 0
        AND t.user_id > 0
        AND t.deleted_at IS NULL
        AND t.archetype = 'regular'
        AND t.created_at::date BETWEEN :date_start AND :date_end
    GROUP BY p.topic_id, t.created_at
    ORDER BY 2 DESC
)

SELECT 
    p.topic_id, 
    fr.created_at::date dt_topic_created,
    (p.created_at - fr.created_at) response_time
FROM posts p
INNER JOIN first_reply fr 
    ON fr.topic_id = p.topic_id 
    AND fr.post_number = p.post_number
    AND p.created_at > fr.created_at
ORDER BY response_time

The results of this query are slightly different than the Time to first response report:
On November 8th for example:
Query: 93 Hours
Report: 116 Hours
Can someone advise?

JammyDodger · 12월 5, 2023, 8:34오전

I think some of these may take some time to research. I’m having a look into it myself to see if I can see what’s going on (though the gap between my SQL skills and my ruby skills is quite large ).

But keep the findings coming as it would be great to firm all this information up.

JammyDodger · 12월 5, 2023, 9:29오전

For the Posts one, I think the stock report is also counting topic posts as well as ones from system users, but also only targeting those with post_type 1 (that is, not whispers, small action posts, or moderator actions)

I think the SQL would look more like this:

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

SELECT 
    p.created_at::date AS "Day",
    COUNT(p.id) AS "Count"
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id AND t.deleted_at ISNULL
WHERE p.created_at::date BETWEEN :start_date AND :end_date
    AND p.deleted_at ISNULL
    AND t.archetype = 'regular'
    AND p.post_type = 1
GROUP BY p.created_at::date
ORDER BY 1

Could you run that on your site and see if it marries up?

Yotam_Hagay · 12월 5, 2023, 1:39오후

Thanks Jammy, will check!
I’m currently working on a Time to first response analysis so I’d appreciate if you could take a look at this one as well.

JammyDodger · 12월 5, 2023, 2:50오후

Having a look at the SQL version, I think it’s missing the AND p.user_id <> t.user_id to exclude replies from the OP. If I add that in it gives me the accurate time between the OP and the first response from someone else:

--[params]
-- date :date_start
-- date :date_end

WITH first_reply AS (
    SELECT 
        p.topic_id, 
        MIN(post_number) post_number, 
        t.created_at
    FROM posts p
    INNER JOIN topics t ON (p.topic_id = t.id)
    WHERE p.deleted_at IS NULL
        AND p.user_id <> t.user_id
        AND p.post_number != 1
        AND p.post_type = 1
        AND p.user_id > 0
        AND t.user_id > 0
        AND t.deleted_at IS NULL
        AND t.archetype = 'regular'
        AND t.created_at::date BETWEEN :date_start AND :date_end
    GROUP BY p.topic_id, t.created_at
    ORDER BY 2 DESC
)

SELECT 
    p.topic_id, 
    fr.created_at::date dt_topic_created,
    (p.created_at - fr.created_at) response_time
FROM posts p
INNER JOIN first_reply fr 
    ON fr.topic_id = p.topic_id 
    AND fr.post_number = p.post_number
    AND p.created_at > fr.created_at
ORDER BY response_time

I also think the stock report is in decimal rather than hours and minutes like the SQL. I’ll have another crack at getting it to match.

Just a small update to include the AVG so it’s more similar to the output of the stock report:

--[params]
-- date :date_start
-- date :date_end

WITH first_reply AS (
    SELECT 
        p.topic_id, 
        MIN(post_number) post_number, 
        t.created_at
    FROM posts p
    INNER JOIN topics t ON p.topic_id = t.id
    WHERE p.deleted_at IS NULL
        AND p.user_id <> t.user_id
        AND p.post_type = 1
        AND p.user_id > 0
        AND t.user_id > 0
        AND t.deleted_at IS NULL
        AND t.archetype = 'regular'
        AND t.created_at::date BETWEEN :date_start AND :date_end
    GROUP BY p.topic_id, t.created_at
)

SELECT 
    fr.created_at::date dt_topic_created,
    AVG(p.created_at - fr.created_at) response_time
FROM posts p
INNER JOIN first_reply fr 
    ON fr.topic_id = p.topic_id 
    AND fr.post_number = p.post_number
    AND p.created_at > fr.created_at
GROUP BY fr.created_at::date
ORDER BY response_time

This seems to marry up with the stock report as long as you take into account that one is in decimal and one in HH:MM. I’m sure there’s a way to convert the SQL response_time to decimal but the HH:MM seems like a more intuitive way of doing it. (I think there are also some extra criteria in there that may not be necessary, but also could be a safety guard against unusual circumstances, so I’ve ultimately left those bits as is until I can say for sure one way or the other )

Could you run this one and see how it matches up?

Yotam_Hagay · 12월 6, 2023, 2:37오후

Yes, it now adds up to the numbers shown in the stock report, thank you!
Just one comment -
I found that the Avg function below brings back missing results in case the time is > 24h (a # of days section is missing I guess).

AVG(p.created_at - fr.created_at)::time response_time

JammyDodger · 12월 6, 2023, 4:11오후

Ah yes, casting to time was a poor choice. If you remove the ::time it will revert back to the more accurate (though harder on the eyes) version.

I’ll edit the one above as well.

글		댓글	조회수
Data Explorer :handshake: AI - Generate Data Explorer queries with AI Announcements data-explorer , ai	0	177	7월 23, 2026
Dashboard New Posts statistics broken Bug	30	905	4월 6, 2025
What would you like to see on your Discourse 2.0 Admin Dashboard? Community Building feedback	88	15440	4월 16, 2018
Admin Reporting & Analysis: Incremental Changes Announcements dashboard-reports , reporting	29	1645	7월 1, 2026
Dashboard Report - Consolidated API Requests Data & reporting sql-query , dashboard-reports , dashboard-sql	0	170	9월 23, 2025