# Statistiche argomenti risolti e non risolti con parametri data e tag
**URL:** https://meta.discourse.org/t/solved-and-unsolved-topic-stats-with-date-and-tag-parameters/301425
**Category:** Data & reporting
**Tags:** solved, sql-query
**Created:** [28 Marzo 2024, 10:27pm UTC](https://meta.discourse.org/t/solved-and-unsolved-topic-stats-with-date-and-tag-parameters/301425 "2024-03-28T22:27:06Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### Post date: [28 Marzo 2024, 10:27pm UTC](https://meta.discourse.org/t/solved-and-unsolved-topic-stats-with-date-and-tag-parameters/301425/1 "2024-03-28T22:27:06Z")
Questo report di [Data Explorer](https://meta.discourse.org/t/32566?silent=true) fornisce un’analisi completa degli argomenti risolti e irrisolti su un sito, entro un intervallo di date specificato e opzionalmente filtrato per un tag specifico.
> :discourse: Questo report richiede che il plugin [Discourse Solved](https://meta.discourse.org/t/discourse-solved/30155) sia abilitato.
Questo report è particolarmente utile per amministratori e moderatori che desiderano comprendere la reattività della community e identificare aree di miglioramento nel supporto e nell’engagement degli utenti.
Statistiche argomenti risolti e irrisolti con parametri data e tag
```sql
--[params]
-- date :start_date = 2022-01-01
-- date :end_date = 2024-01-01
-- text :tag_name = all
WITH valid_topics AS (
SELECT
t.id,
t.user_id,
t.title,
t.views,
(SELECT COUNT(*) FROM posts WHERE topic_id = t.id AND deleted_at IS NULL AND post_type = 1) - 1 AS "posts_count",
t.created_at,
(CURRENT_DATE::date - t.created_at::date) AS "total_days",
STRING_AGG(tags.name, ', ') AS tag_names, -- Aggrega i tag per ogni argomento
c.name AS category_name
FROM topics t
LEFT JOIN topic_tags tt ON tt.topic_id = t.id
LEFT JOIN tags ON tags.id = tt.tag_id
LEFT JOIN categories c ON c.id = t.category_id
WHERE t.deleted_at IS NULL
AND t.created_at::date BETWEEN :start_date AND :end_date
AND t.archetype = 'regular'
GROUP BY t.id, c.name
),
solved_topics AS (
SELECT
vt.id,
dsst.created_at
FROM discourse_solved_solved_topics dsst
INNER JOIN valid_topics vt ON vt.id = dsst.topic_id
),
last_reply AS (
SELECT p.topic_id, p.user_id FROM posts p
INNER JOIN (SELECT topic_id, MAX(id) AS post FROM posts p
WHERE deleted_at IS NULL
AND post_type = 1
GROUP BY topic_id) x ON x.post = p.id
),
first_reply AS (
SELECT p.topic_id, p.user_id, p.created_at FROM posts p
INNER JOIN (SELECT topic_id, MIN(id) AS post FROM posts p
WHERE deleted_at IS NULL
AND post_type = 1
AND post_number > 1
GROUP BY topic_id) x ON x.post = p.id
)
SELECT
CASE
WHEN st.id IS NOT NULL THEN 'solved'
ELSE 'unsolved'
END AS status,
vt.tag_names,
vt.category_name,
vt.id AS topic_id,
vt.user_id AS topic_user_id,
ue.email,
vt.title,
vt.views,
lr.user_id AS last_reply_user_id,
ue2.email AS last_reply_user_email,
vt.created_at::date AS topic_create,
COALESCE(TO_CHAR(fr.created_at, 'YYYY-MM-DD'), '') AS first_reply_create,
COALESCE(TO_CHAR(st.created_at, 'YYYY-MM-DD'), '') AS solution_create,
COALESCE(fr.created_at::date - vt.created_at::date, 0) AS "time_first_reply(days)",
COALESCE(CEIL(EXTRACT(EPOCH FROM (fr.created_at - vt.created_at)) / 3600.00), 0) AS "time_first_reply(hours)",
COALESCE(st.created_at::date - vt.created_at::date, 0) AS "time_solution(days)",
COALESCE(CEIL(EXTRACT(EPOCH FROM (st.created_at - vt.created_at)) / 3600.00), 0) AS "time_solution(hours)",
vt.created_at::date,
vt.posts_count AS number_of_replies,
vt.total_days AS total_days_without_solution
FROM valid_topics vt
LEFT JOIN last_reply lr ON lr.topic_id = vt.id
LEFT JOIN first_reply fr ON fr.topic_id = vt.id
LEFT JOIN solved_topics st ON st.id = vt.id
INNER JOIN user_emails ue ON vt.user_id = ue.user_id AND ue."primary" = true
LEFT JOIN user_emails ue2 ON lr.user_id = ue2.user_id AND ue2."primary" = true
WHERE (:tag_name = 'all' OR vt.tag_names ILIKE '%' || :tag_name || '%')
GROUP BY st.id, vt.tag_names, vt.category_name, vt.id, vt.user_id, ue.email, vt.title, vt.views, lr.user_id, ue2.email, vt.created_at, fr.created_at, st.created_at, vt.posts_count, vt.total_days
ORDER BY topic_create, vt.total_days DESC
```
### Spiegazione della query SQL
La reportistica viene generata tramite una complessa query SQL che utilizza Common Table Expressions (CTE) per organizzare ed elaborare i dati in modo efficiente. La query è strutturata come segue:
- **valid\_topics** : Questa CTE filtra gli argomenti in base all’intervallo di date specificato e all’archetipo (‘regular’), escludendo gli argomenti eliminati. Aggrega anche i tag associati a ciascun argomento per un successivo filtraggio per nome del tag, se specificato.
- **solved\_topics** : Identifica gli argomenti contrassegnati come risolti.
- **last\_reply** : Determina l’utente che ha effettuato l’ultima risposta in ciascun argomento trovando l’ID del post massimo (che indica il post più recente) che non è eliminato ed è di tipo post 1 (indicando un post regolare).
- **first\_reply** : Simile a last\_reply, ma identifica il primo utente a rispondere all’argomento dopo il post originale.
La query principale combina quindi queste CTE per compilare un report dettagliato su ciascun argomento, includendo se è risolto o irrisolto, nomi dei tag, nome della categoria, ID argomento e utente, email, visualizzazioni, conteggio delle risposte e tempistiche per la prima risposta e la soluzione.
#### Parametri
- **start\_date** : L’inizio dell’intervallo di date per cui generare il report.
- **end\_date** : La fine dell’intervallo di date per cui generare il report.
- **tag\_name** : Il tag specifico per filtrare gli argomenti. Utilizzare ‘all’ per includere argomenti con qualsiasi tag.
#### Risultati
Il report fornisce le seguenti informazioni per ciascun argomento all’interno dei parametri specificati:
- **status** : Indica se l’argomento è stato risolto o rimane irrisolto.
- **tag\_names** : Mostra i tag associati all’argomento.
- **category\_name** : Mostra la categoria associata all’argomento.
- **topic\_id** : L’identificatore univoco dell’argomento.
- **topic\_user\_id** : L’ID dell’utente che ha creato l’argomento.
- **user\_email** : L’indirizzo email del creatore dell’argomento.
- **title** : Il titolo dell’argomento.
- **views** : Il numero di visualizzazioni ricevute dall’argomento.
- **last\_reply\_user\_id** : L’ID dell’utente che ha effettuato l’ultima risposta all’argomento.
- **last\_reply\_user\_email** : L’indirizzo email dell’utente che ha effettuato l’ultima risposta.
- **topic\_create** : La data di creazione dell’argomento.
- **first\_reply\_create** : La data della prima risposta all’argomento.
- **solution\_create** : La data in cui è stata contrassegnata una soluzione per l’argomento (se applicabile).
- **time\_first\_reply(days/hours)**: Il tempo impiegato per ricevere la prima risposta, in giorni e ore.
- **time\_solution(days/hours)**: Il tempo impiegato per risolvere l’argomento, in giorni e ore.
- **created\_at** : La data di creazione dell’argomento.
- **number\_of\_replies** : Il numero totale di risposte all’argomento.
- **total\_days\_without\_solution** : Il numero totale di giorni in cui l’argomento è stato attivo senza una soluzione.
### Risultati di esempio
| status | tag\_names | category\_name | topic\_id | topic\_user\_id | user\_email | title | views | last\_reply\_user\_id | last\_reply\_user\_email | topic\_create | first\_reply\_create | solution\_create | time\_first\_reply(days) | time\_first\_reply(hours) | time\_solution(days) | time\_solution(hours) | created\_at | number\_of\_replies | total\_days\_without\_solution |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| solved | support, password | category1 | 101 | 1 | [user1@example.com](mailto:user1@example.com) | Come reimpostare la mia password? | 150 | 3 | [user3@example.com](mailto:user3@example.com) | 2022-01-05 | 2022-01-06 | 2022-01-07 | 1 | 24 | 2 | 48 | 2022-01-05 | 5 | 2 |
| unsolved | support, account | category2 | 102 | 2 | [user2@example.com](mailto:user2@example.com) | Problema con l’attivazione dell’account | 75 | 4 | [user4@example.com](mailto:user4@example.com) | 2022-02-10 | 2022-02-12 | | 2 | 48 | 0 | 0 | 2022-02-10 | 3 | 412 |
| solved | support | category3 | 103 | 5 | [user5@example.com](mailto:user5@example.com) | Impossibile caricare l’immagine del profilo | 200 | 6 | [user6@example.com](mailto:user6@example.com) | 2022-03-15 | 2022-03-16 | 2022-03-18 | 1 | 24 | 3 | 72 | 2022-03-15 | 8 | 3 |
| unsolved | NULL | category4 | 104 | 7 | [user7@example.com](mailto:user7@example.com) | Errore durante la pubblicazione | 50 | 8 | [user8@example.com](mailto:user8@example.com) | 2022-04-20 | | | 0 | 0 | 0 | 0 | 2022-04-20 | 0 | 373 |
---
### Author:  [@tknospdr](https://meta.discourse.org/u/tknospdr)
#### Post date: [12 Settembre 2025, 1:41pm UTC](https://meta.discourse.org/t/solved-and-unsolved-topic-stats-with-date-and-tag-parameters/301425/2 "2025-09-12T13:41:08Z")
Un’altra fantastica query e un’altra richiesta da parte mia. 🙂
Puoi creare un campo di selezione per restringere la categoria/sottocategoria?
Mi piacerebbe poter eseguire questo report solo sulla categoria dei miei ticket.
---
### Author:  [@tknospdr](https://meta.discourse.org/u/tknospdr)
#### Post date: [12 Settembre 2025, 1:50pm UTC](https://meta.discourse.org/t/solved-and-unsolved-topic-stats-with-date-and-tag-parameters/301425/3 "2025-09-12T13:50:37Z")
Inoltre, ho trovato un caso limite insolito. Potresti essere in grado o meno di tenerne conto, ma non c’è danno nel chiedere.
Ho un argomento a cui ho risposto e l’ho contrassegnato come soluzione il giorno dopo la sua pubblicazione. Poi un altro tecnico ha dato una risposta diversa e ha contrassegnato quella come soluzione circa 10 giorni dopo.
Il report mostra il tempo alla soluzione come 1 giorno ma il tempo totale senza soluzione come 10 giorni.
---
### Author:  [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### Post date: [18 Settembre 2025, 9:57pm UTC](https://meta.discourse.org/t/solved-and-unsolved-topic-stats-with-date-and-tag-parameters/301425/4 "2025-09-18T21:57:39Z")
Ciao @tknospdr,
Per rispondere a entrambe le tue domande qui:
> [@tknospdr](#):
>
> Puoi creare un campo di selezione per restringere categoria/sottocategoria.
> [@tknospdr](#):
>
> Il report mostra il tempo per la soluzione come 1 giorno ma il tempo totale senza soluzione come 10 giorni.
Puoi usare la seguente query per affrontare questo problema:
```sql
--[params]
-- date :start_date = 2022-01-01
-- date :end_date = 2024-01-01
-- text :tag_name = all
-- null category_id :category_id
WITH valid_topics AS (
SELECT
t.id,
t.user_id,
t.title,
t.views,
(SELECT COUNT(*) FROM posts WHERE topic_id = t.id AND deleted_at IS NULL AND post_type = 1) - 1 AS "posts_count",
t.created_at,
(CURRENT_DATE::date - t.created_at::date) AS "total_days",
STRING_AGG(tags.name, ', ') AS tag_names,
c.name AS category_name,
t.category_id
FROM topics t
LEFT JOIN topic_tags tt ON tt.topic_id = t.id
LEFT JOIN tags ON tags.id = tt.tag_id
LEFT JOIN categories c ON c.id = t.category_id
WHERE t.deleted_at IS NULL
AND t.created_at::date BETWEEN :start_date AND :end_date
AND t.archetype = 'regular'
GROUP BY t.id, c.name, t.category_id
),
solved_topics AS (
SELECT
dsst.topic_id,
MIN(dsst.created_at) AS first_solution_at, -- Get earliest solution
MAX(dsst.created_at) AS latest_solution_at -- Get latest solution
FROM discourse_solved_solved_topics dsst
GROUP BY dsst.topic_id
),
last_reply AS (
SELECT p.topic_id, p.user_id FROM posts p
INNER JOIN (SELECT topic_id, MAX(id) AS post FROM posts p
WHERE deleted_at IS NULL
AND post_type = 1
GROUP BY topic_id) x ON x.post = p.id
),
first_reply AS (
SELECT p.topic_id, p.user_id, p.created_at FROM posts p
INNER JOIN (SELECT topic_id, MIN(id) AS post FROM posts p
WHERE deleted_at IS NULL
AND post_type = 1
AND post_number > 1
GROUP BY topic_id) x ON x.post = p.id
)
SELECT
CASE
WHEN st.topic_id IS NOT NULL THEN 'solved'
ELSE 'unsolved'
END AS status,
vt.tag_names,
vt.category_name,
vt.id AS topic_id,
vt.user_id AS topic_user_id,
ue.email,
vt.title,
vt.views,
lr.user_id AS last_reply_user_id,
ue2.email AS last_reply_user_email,
vt.created_at::date AS topic_create,
COALESCE(TO_CHAR(fr.created_at, 'YYYY-MM-DD'), '') AS first_reply_create,
COALESCE(TO_CHAR(st.first_solution_at, 'YYYY-MM-DD'), '') AS first_solution_create,
COALESCE(TO_CHAR(st.latest_solution_at, 'YYYY-MM-DD'), '') AS latest_solution_create,
COALESCE(fr.created_at::date - vt.created_at::date, 0) AS "time_first_reply(days)",
COALESCE(CEIL(EXTRACT(EPOCH FROM (fr.created_at - vt.created_at)) / 3600.00), 0) AS "time_first_reply(hours)",
COALESCE(st.first_solution_at::date - vt.created_at::date, 0) AS "time_to_first_solution(days)",
COALESCE(CEIL(EXTRACT(EPOCH FROM (st.first_solution_at - vt.created_at)) / 3600.00), 0) AS "time_to_first_solution(hours)",
COALESCE(st.latest_solution_at::date - vt.created_at::date, 0) AS "time_to_latest_solution(days)",
COALESCE(CEIL(EXTRACT(EPOCH FROM (st.latest_solution_at - vt.created_at)) / 3600.00), 0) AS "time_to_latest_solution(hours)",
vt.created_at::date,
vt.posts_count AS number_of_replies,
CASE
WHEN st.topic_id IS NULL THEN vt.total_days
ELSE COALESCE(st.latest_solution_at::date - vt.created_at::date, 0)
END AS total_days_without_solution
FROM valid_topics vt
LEFT JOIN last_reply lr ON lr.topic_id = vt.id
LEFT JOIN first_reply fr ON fr.topic_id = vt.id
LEFT JOIN solved_topics st ON st.topic_id = vt.id
INNER JOIN user_emails ue ON vt.user_id = ue.user_id AND ue."primary" = true
LEFT JOIN user_emails ue2 ON lr.user_id = ue2.user_id AND ue2."primary" = true
WHERE (:tag_name = 'all' OR vt.tag_names ILIKE '%' || :tag_name || '%')
AND (:category_id ISNULL OR vt.category_id = :category_id)
GROUP BY st.topic_id, st.first_solution_at, st.latest_solution_at, vt.tag_names, vt.category_name, vt.id, vt.user_id, ue.email, vt.title, vt.views, lr.user_id, ue2.email, vt.created_at, fr.created_at, vt.posts_count, vt.total_days
ORDER BY topic_create, vt.total_days DESC
```
Dove il parametro `-- null category_id :category_id` può essere utilizzato per selezionare (opzionalmente) una categoria per eseguire il report, e i risultati tracciano sia la prima che l’ultima soluzione.
Inoltre, il risultato `total_days_without_solution` utilizzerà ora la data dell’ultima soluzione invece della prima.
---
### Author:  [@tknospdr](https://meta.discourse.org/u/tknospdr)
#### Post date: [19 Settembre 2025, 8:22pm UTC](https://meta.discourse.org/t/solved-and-unsolved-topic-stats-with-date-and-tag-parameters/301425/5 "2025-09-19T20:22:12Z")
Fantastico, grazie! Sembra ottimo.