Guida di riferimento per i report della dashboard di amministrazione

[Discourse]

Questa è una guida di riferimento che descrive il funzionamento dei Report della Dashboard di Amministrazione, i dati che visualizzano, le corrispondenti query SQL di Data Explorer e dove trovare il codice Ruby per ogni report.

Livello utente richiesto: Staff

Discourse include diversi Report della dashboard di amministrazione integrati, che possono essere utili per esplorare le statistiche di una community. Per accedere a questi report, puoi visitare discourse.example.com/admin/dashboard/reports sul tuo sito (o fare clic sul link Reports nella barra laterale). Tieni presente che solo gli utenti staff avranno accesso a questi report.

I dati di tutti gli utenti di un sito sono inclusi in questi report (inclusa l’attività dello staff come la visita delle pagine di amministrazione). L’unica condizione imposta agli utenti nei report è che siano utenti “reali”, il che viene utilizzato per escludere l’utente system dai report.

I plugin possono anche aggiungere report alla dashboard con add_report(name, &block).

I modelli Ruby per la maggior parte dei report si trovano in: discourse/app/models/concerns/reports/. Alcuni report fanno anche riferimento a: discourse/app/models/report.rb

I topic dashboard-sql contengono tutte le corrispondenti query SQL che possono essere utilizzate per generare report identici ai Report della Dashboard di Amministrazione. Queste query possono essere utilizzate all’interno del plugin Data Explorer e per Eseguire query Data Explorer con l’API di Discourse

Per nascondere report specifici dalla dashboard, utilizza l’impostazione del sito dashboard_hidden_reports.

Soluzioni accettate

Visualizza il conteggio giornaliero dei post contrassegnati come soluzioni.

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

Query SQL: Dashboard Report - Accepted Solutions

Accessi degli Amministratori

Elenco degli orari di accesso degli amministratori con le relative posizioni.

Codice Ruby: discourse/app/models/concerns/reports/staff_logins.rb

Query SQL: Dashboard Report - Admin Logins

Anonimi

Numero di nuove visualizzazioni di pagina da parte di visitatori non registrati in un account.

Codice Ruby: discourse/app/models/concerns/reports/consolidated_page_views.rb

Query SQL: Dashboard Report - Anonymous

Segnalibri

Numero di nuovi topic e post aggiunti ai segnalibri.

Codice Ruby: discourse/app/models/concerns/reports/bookmarks.rb

Query SQL: Dashboard Report - Bookmarks

Richieste API consolidate

Statistiche sull’uso dell’API per data, che tracciano sia le richieste API regolari sia le richieste API degli utenti.

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

Query SQL: Dashboard Report - Consolidated API Requests

Visualizzazioni di pagina consolidate

Visualizzazioni di pagina per utenti registrati, utenti anonimi e crawler. Questo è un report legacy sostituito dal report Site Traffic.

Codice Ruby: discourse/app/models/concerns/reports/consolidated_page_views.rb

Query SQL: Dashboard Report - Consolidated Pageviews

Visualizzazioni di pagina consolidate con rilevamento del browser (Non supportato)

Visualizzazioni di pagina per utenti registrati, utenti anonimi, crawler noti e altri. Questo report è non supportato e ora delega al report Site Traffic.

Codice Ruby: discourse/app/models/concerns/reports/consolidated_page_views_browser_detection.rb

Query SQL: Dashboard Report - Consolidated Pageviews with Browser Detection

DAU/MAU

Numero di membri che hanno effettuato l’accesso negli ultimi giorni diviso per il numero di membri che hanno effettuato l’accesso negli ultimi mesi – restituisce una percentuale che indica la “fedeltà” della community. Obiettivo: >20%.

Codice Ruby: discourse/app/models/concerns/reports/dau_by_mau.rb

Query SQL: Dashboard Report - DAU/MAU

Utenti attivi giornalieri

Numero di utenti che hanno messo “mi piace” o pubblicato negli ultimi giorni.

Codice Ruby: discourse/app/models/concerns/reports/daily_engaged_users.rb

Query SQL: Dashboard Report - Daily Engaged Users

Email inviate

Numero di nuove email inviate.

Codice Ruby: discourse/app/models/concerns/reports/emails.rb

Query SQL: Dashboard Report - Emails Sent

Segnalazioni

Numero di nuove segnalazioni.

Codice Ruby: discourse/app/models/concerns/reports/flags.rb

Query SQL: Dashboard Report - Flags

Stato delle segnalazioni

Elenco degli stati delle segnalazioni, incluso il tipo di segnalazione, l’autore, chi ha segnalato e il tempo di risoluzione.

Codice Ruby: discourse/app/models/concerns/reports/flags_status.rb

Query SQL: Dashboard Report - Flags Status

Mi piace

Numero di nuovi “mi piace”.

Codice Ruby: discourse/app/models/concerns/reports/likes.rb

Query SQL: Dashboard Report - Likes

Utenti registrati

Numero di nuove visualizzazioni di pagina da parte di utenti registrati.

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

Query SQL: Dashboard Report - Logged In

Attività dei Moderatori

Elenco delle attività dei moderatori, incluse le segnalazioni esaminate, il tempo di lettura, i topic creati, i post creati, i messaggi privati creati e le revisioni.

Query SQL: Dashboard Report - Moderator Activity

Avvisi dei Moderatori

Numero di avvisi inviati tramite messaggi privati dai moderatori.

Codice Ruby: discourse/app/models/concerns/reports/moderator_warning_private_messages.rb

Query SQL: Dashboard Report - Moderator Warnings

Nuovi contributori

Numero di utenti che hanno pubblicato il loro primo post durante questo periodo.

Codice Ruby: discourse/app/models/concerns/reports/new_contributors.rb

Query SQL: Dashboard Report - New Contributors

Notifica Moderatori

Numero di volte in cui i moderatori sono stati notificati privatamente da una segnalazione.

Codice Ruby: discourse/app/models/concerns/reports/notify_moderators_private_messages.rb

Query SQL: Dashboard Report - Notify Moderators

Notifica Utente

Numero di volte in cui gli utenti sono stati notificati privatamente da una segnalazione.

Codice Ruby: discourse/app/models/concerns/reports/notify_user_private_messages.rb

Query SQL: Dashboard Report - Notify User

Sentiment generale

Numero di post classificati come positivi o negativi dall’IA “Sentiment”, in un periodo specifico.

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

Query SQL: Dashboard Report - Overall Sentiment

Visualizzazioni di pagina

Numero di nuove visualizzazioni di pagina da parte di tutti i visitatori. Identico al totale di Visualizzazioni di pagina consolidate.

Discourse utilizza la seguente query per determinare il totale delle visualizzazioni di pagina:

Query SQL: Dashboard Report - Consolidated Pageviews

Modifiche ai post

Numero di nuove modifiche ai post.

Codice Ruby: discourse/app/models/concerns/reports/post_edits.rb

Query SQL: Dashboard Report - Post Edits

Post

Nuovi post creati durante il periodo di tempo selezionato

Codice Ruby: discourse/app/models/concerns/reports/posts.rb

Query SQL: Dashboard Report - Posts

Emozione dei post

Numero di post classificati dall’IA con una delle seguenti emozioni: Tristezza, Sorpresa, Paura, Rabbia, Gioia, Disgusto - raggruppati per livello di fiducia dell’autore, in un periodo specifico.

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

Query SQL: Dashboard Report - Post Emotion

Reazioni

Elenco delle reazioni più recenti.

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

Query SQL: Dashboard Report - Reactions

Registrazioni

Nuove registrazioni di account per questo periodo.

Codice Ruby: discourse/app/models/concerns/reports/signups.rb

Query SQL: Dashboard Report - Signups

Traffico del sito

Visualizzazioni di pagina per browser registrati, browser anonimi, crawler e altro traffico. Questo è il report principale sul traffico, che sostituisce i report legacy Visualizzazioni di pagina consolidate.

Codice Ruby: discourse/app/models/concerns/reports/site_traffic.rb

Query SQL: Dashboard Report - Site Traffic

Accessi sospetti

Dettagli di nuovi accessi che differiscono in modo sospetto dagli accessi precedenti.

Codice Ruby: discourse/app/models/concerns/reports/suspicious_logins.rb

Query SQL: Dashboard Report - Suspicious Logins

Sistema

Numero di messaggi privati inviati automaticamente dal Sistema.

Codice Ruby: discourse/app/models/concerns/reports/system_private_messages.rb

Query SQL: Dashboard Report - System

Tempo per la prima risposta

Tempo medio (in ore) della prima risposta ai nuovi topic.

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

Query SQL: Dashboard Report - Time to First Response

Utenti più ignorati / silenziati

Utenti che sono stati silenziati e/o ignorati da molti altri utenti.

Codice Ruby: discourse/app/models/concerns/reports/top_ignored_users.rb

Query SQL: Dashboard Report - Top Ignored / Muted Users

Topic più referenziati

Topic che hanno ricevuto il maggior numero di clic da fonti esterne.

Codice Ruby: discourse/app/models/concerns/reports/top_referred_topics.rb

Query SQL: Dashboard Report - Top Referred Topics

Referenti principali

Utenti elencati in base al numero di clic sui link che hanno condiviso.

Codice Ruby: discourse/app/models/concerns/reports/top_referrers.rb

Query SQL: Dashboard Report - Top Referrers

Fonti di traffico principali

Fonti esterne che hanno linkato a questo sito con maggiore frequenza.

Codice Ruby: discourse/app/models/concerns/reports/top_traffic_sources.rb

Query SQL: Dashboard Report - Top Traffic Sources

Caricamenti principali

Elenca tutti i caricamenti per estensione, dimensione del file e autore.

Codice Ruby: discourse/app/models/concerns/reports/top_uploads.rb

Query SQL: Dashboard Report - Top Uploads

Utenti principali per “mi piace” ricevuti

Top 10 utenti che hanno ricevuto “mi piace”.

Codice Ruby: discourse/app/models/concerns/reports/top_users_by_likes_received.rb

Query SQL: Dashboard Report - Top Users by Likes Received

Utenti principali per “mi piace” ricevuti da utenti con un livello di fiducia inferiore

Top 10 utenti con un livello di fiducia superiore che vengono apprezzati da persone con un livello di fiducia inferiore.

Codice Ruby: discourse/app/models/concerns/reports/top_users_by_likes_received_from_inferior_trust_level.rb

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

Utenti principali per “mi piace” ricevuti da una varietà di persone

Top 10 utenti che hanno ricevuto “mi piace” da un’ampia gamma di persone.

Codice Ruby: discourse/app/models/concerns/reports/top_users_by_likes_received_from_a_variety_of_people.rb

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

Topic

Nuovi topic creati durante questo periodo.

Codice Ruby: discourse/app/models/concerns/reports/topics.rb

Query SQL: Dashboard Report - Topics

Topic senza risposta

Numero di nuovi topic creati che non hanno ricevuto una risposta.

Codice Ruby: discourse/app/models/concerns/reports/topics_with_no_response.rb

Query SQL: Dashboard Report - Topics with No Response

Statistiche visualizzazioni topic

Top 100 topic per visualizzazioni con ripartizione tra utenti anonimi e registrati, filtrabile per categoria.

Codice Ruby: discourse/app/models/concerns/reports/topic_view_stats.rb

Query SQL: Dashboard Report - Topic View Stats

Termini di ricerca in tendenza

Termini di ricerca più popolari con i relativi tassi di clic.

Codice Ruby: discourse/app/models/concerns/reports/trending_search.rb

Query SQL: Dashboard Report - Trending Search Terms

Crescita del livello di fiducia

Numero di utenti che hanno aumentato il loro livello di fiducia durante questo periodo.

Il report sulla crescita del livello di fiducia recupera i dati dalla tabella user_histories nel database di Discourse. Specificamente, questo report conta il numero di volte in cui viene registrato un user_histories.action per un aumento del livello di fiducia di un utente.

Codice Ruby: discourse/app/models/concerns/reports/trust_level_growth.rb

Query SQL: Dashboard Report - Trust Level Growth

Politiche non accettate

Questo report della dashboard identifica i topic con politiche non accettate da determinati utenti.

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

Query SQL: Dashboard Report - Unaccepted Policies

Rapporto di segnalazione degli utenti

Elenco di utenti ordinati per rapporto di risposta dello staff alle loro segnalazioni (da non concordato a concordato).

Codice Ruby: discourse/app/models/concerns/reports/user_flagging_ratio.rb

Query SQL: Dashboard Report - User Flagging Ratio

Note utente

Elenco delle note utente più recenti.

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

Query SQL: Dashboard Report - User Notes

Visualizzazioni profili utente

Totale delle nuove visualizzazioni dei profili utente.

Codice Ruby: discourse/app/models/concerns/reports/profile_views.rb

Query SQL: Dashboard Report - User Profile Views

Visite degli utenti

Il numero totale di visite di utenti registrati al forum per il periodo di tempo selezionato (oggi, ieri, ultimi 7 giorni, ecc).

Una visita utente viene conteggiata ogni volta che un utente registrato univoco visita il sito, fino a una volta al giorno. Ad esempio, se un utente ha visitato un sito ogni giorno entro una settimana, Discourse lo conteggerà come 7 visite utente.

Codice Ruby: discourse/app/models/concerns/reports/visits.rb

Query SQL: Dashboard Report - User Visits

Visite degli utenti (mobile)

Numero di utenti registrati univoci che hanno visitato utilizzando un dispositivo mobile.

Codice Ruby: discourse/app/models/concerns/reports/mobile_visits.rb

Query SQL: Dashboard Report - User Visits

Utente a Utente (escludendo le risposte)

Numero di nuovi messaggi privati avviati.

Codice Ruby: discourse/app/models/concerns/reports/user_to_user_private_messages.rb

Query SQL: Dashboard Report - User-to-User

Utente a Utente (con risposte)

Numero di tutti i nuovi messaggi privati e risposte.

Codice Ruby: discourse/app/models/concerns/reports/user_to_user_private_messages_with_replies.rb

Query SQL: Dashboard Report - User-to-User

Utenti per livello di fiducia

Numero di utenti raggruppati per livello di fiducia.

Codice Ruby: discourse/app/models/concerns/reports/users_by_trust_level.rb

Query SQL: Dashboard Report - Users Per Trust Level

Utenti per tipo

Numero di utenti raggruppati per amministratore, moderatore, sospeso e silenziato.

Codice Ruby: discourse/app/models/concerns/reports/users_by_type.rb

Query SQL: Dashboard Report - Users Per Type

Visualizzazioni di pagina dai crawler web

Totale delle visualizzazioni di pagina dai crawler web nel tempo.

Codice Ruby: discourse/app/models/report.rb

Query SQL: Dashboard Report - Web Crawler Pageviews

User Agent dei crawler web

Elenco degli user agent dei crawler web, ordinato per visualizzazioni di pagina.

Codice Ruby: discourse/app/models/concerns/reports/web_crawlers.rb

Query SQL: Dashboard Report - Web Crawler User Agents

[pfaffman (Jay Pfaffman)]

@SaraDev

Discourse contiene diversi report integrati nella dashboard di amministrazione che possono essere utili per esplorare le statistiche di una community. Per accedere a questi report, puoi visitare /admin/dashboard/reports sul tuo sito.

Non vedo un link a questo su /admin. Sto sbagliando a leggere? Sembrerebbe che questo dovrebbe essere più facile da trovare. Penso di aver saputo che questi report erano qui ma ho cercato e non sono riuscito a trovarli.

Anche se mi ci sono voluti solo pochi minuti per trovarli, potrebbe essere utile aggiungere qualcosa come

I plugin possono aggiungere report alla dashboard con add_report(name, &block).

[maiki]

@pfaffman

Non vedo un link a questo su /admin.

@pfaffman

Sembrerebbe che questo dovrebbe essere più facile da scoprire. Penso di sapere che questi report fossero qui ma ho cercato e non sono riuscito a trovarli.

Sì, potrebbe essere carino in un PM allo staff menzionarli quando un sito viene creato per la prima volta.

[pfaffman (Jay Pfaffman)]

Mi dispiace. Pensavo sicuramente di averlo già visto da qualche parte.

Non si riesce a far leggere le cose alle persone… . . . A meno che non possa leggere il codice sorgente per scoprire come farlo in un plugin?

Ma forse aggiornare quanto sopra a

@SaraDev

. Per accedere a questi report, puoi visitare /admin/dashboard/reports sul tuo sito o fare clic sul link report nella dashboard.

Penso che questo sia ciò che mi ha davvero confuso. (Ma no, non ho scuse.)

[maiki]

@pfaffman

Ma forse aggiorna quanto sopra a

Ho reso il topic un wiki, vai!

[hello-smile6 (9pfs)]

@SaraDev

Puntare a >30%.

Ciò non corrisponde all’interfaccia utente (l’interfaccia utente utilizza il 20%), quale dovrebbe essere?

[JammyDodger]

Ottima osservazione. È stato recentemente aggiornato al 20%. Apporterò la modifica nell’OP.

Is there a difference in target retention rate between a support-first forum and discussion-first forum?

Here’s a PR with that change: Longer term, I think a more nuanced approach could be powerful. I’d love for communities to be able to set their own goals (with some guidance like what you shared above about why they might choose one vs. the other) and use the dashboard or other related features to help them track their results vs. what they are aiming for.

[Yotam_Hagay]

@SaraDev

Principali fonti di traffico

Fonti esterne che hanno collegato più frequentemente a questo sito.

Codice Ruby: discourse/app/models/concerns/reports/top_traffic_sources.rb

Ciao @SaraDev È possibile ottenere l’output di questo report con una query SQL? Puoi condividerla?
Grazie

[SaraDev (Sara Devlaeminck)]

@Yotam_Hagay

Ciao @SaraDev È possibile ottenere l’output di questo report con una query SQL? Puoi condividerla?
Grazie

Sì, puoi usare il seguente report SQL per le principali fonti di traffico:

-- [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

Con questa query, nota che i parametri di data accettano date nel formato giorno/mese/anno.

[Yotam_Hagay]

@SaraDev

Sì, puoi usare il seguente report SQL per le principali origini del traffico:

Ciao @SaraDev, grazie per aver condiviso la query.
Una domanda più generale riguardo a questo report e alla tabella incoming_links in realtà: rappresenta solo il traffico per le pagine dei post e non il traffico verso tutte le pagine del forum, giusto?

Contesto: sto cercando di analizzare le tendenze del traffico generale del forum e speravo di ottenere il traffico generale per origini dal report delle principali origini del traffico.
Ma mentre il traffico generale è di circa 272K nell’ultimo mese (utenti e anonimi), i click totali nel report delle origini del traffico per lo stesso periodo sono solo 59K.
Inoltre, vedo che utilizzi un inner join con le tabelle topics e posts, il che significa che se non c’è un post_id associato al click, non lo conti.

Puoi per favore confermare la mia conclusione e magari spiegare un po’ la logica dietro la tabella incoming_links?

[Yotam_Hagay]

@SaraDev

Post

Nuovi post creati durante questo periodo

Ciao @SaraDev ho eseguito questa query e il risultato non corrisponde esattamente al report Post nella scheda generale.
Ad esempio per il 30 novembre:
Query = 112 post
Report = 120 post
Puoi controllare la differenza?
Grazie

[JammyDodger]

Solo per tua informazione @Yotam_Hagay - anche se Sara è l’OP, la guida è responsabilità di tutti Non c’è bisogno di una @mention su ogni post.

[Yotam_Hagay]

Grazie @JammyDodger per il chiarimento.
C’è qualcun altro che posso taggare o a cui posso rivolgermi per ottenere una risposta?

[Yotam_Hagay]

@SaraDev

Tempo SQL per la prima risposta

--[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

I risultati di questa query sono leggermente diversi dal report Time to first response:
L’8 novembre, ad esempio:
Query: 93 ore
Report: 116 ore
Qualcuno può darmi un consiglio?

[JammyDodger]

Penso che alcune di queste potrebbero richiedere del tempo per essere ricercate. Ci sto dando un’occhiata anch’io per vedere se riesco a capire cosa sta succedendo (anche se il divario tra le mie competenze SQL e le mie competenze Ruby è piuttosto ampio ).

Ma continua a fornire i risultati in quanto sarebbe fantastico consolidare tutte queste informazioni.

[JammyDodger]

@Yotam_Hagay

Ciao @SaraDev ho eseguito questa query e il risultato non corrisponde esattamente al report dei post nella scheda generale.
Ad esempio per il 30 novembre:
Query = 112 post
Report = 120 post
Puoi controllare la differenza?
Grazie

Per quello dei post, penso che il report di magazzino conti anche i post degli argomenti e quelli degli utenti di sistema, ma si rivolge solo a quelli con post_type 1 (cioè, non sussurri, piccoli post di azione o azioni del moderatore).

Penso che l’SQL dovrebbe essere più simile a questo:

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

SELECT
    p.created_at::date AS "Giorno",
    COUNT(p.id) AS "Conteggio"
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

Potresti eseguirlo sul tuo sito e vedere se corrisponde?

[Yotam_Hagay]

Grazie Jammy, controllerò!
Sto attualmente lavorando su un’analisi del tempo di prima risposta, quindi apprezzerei se potessi dare un’occhiata anche a questa.

[JammyDodger]

Guardando la versione SQL, penso che manchi AND p.user_id <> t.user_id per escludere le risposte dall’OP. Se lo aggiungo, ottengo il tempo accurato tra l’OP e la prima risposta di qualcun altro:

--[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

Penso anche che il report di magazzino sia in decimale anziché in ore e minuti come l’SQL. Ci riproverò per farlo corrispondere.

---

Solo un piccolo aggiornamento per includere AVG in modo che sia più simile all’output del report di magazzino:

--[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

Questo sembra corrispondere al report di magazzino, purché si tenga conto che uno è in decimale e l’altro in HH:MM. Sono sicuro che ci sia un modo per convertire il response_time SQL in decimale, ma HH:MM sembra un modo più intuitivo per farlo. (Penso che ci siano anche alcuni criteri aggiuntivi che potrebbero non essere necessari, ma potrebbero anche essere una protezione contro circostanze insolite, quindi alla fine ho lasciato quelle parti così come sono finché non potrò dirlo con certezza )

Potresti eseguirlo e vedere come corrisponde?

[Yotam_Hagay]

Sì, ora corrisponde ai numeri mostrati nel report di magazzino, grazie!
Solo un commento:
Ho scoperto che la funzione AVG sottostante restituisce risultati mancanti nel caso in cui il tempo sia > 24 ore (manca una sezione # di giorni, immagino).

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

[JammyDodger]

Sì, il casting a time è stata una scelta sbagliata. Se rimuovi ::time, tornerà alla versione più accurata (anche se più difficile da leggere).

Modificherò anche quella sopra.