# Dashboard-Bericht - Themen ohne Antwort

**URL:** <https://meta.discourse.org/t/dashboard-report-topics-with-no-response/294002>\
**Category:** Data & reporting\
**Tags:** sql-query, dashboard-reports, dashboard-sql\
**Created:** [2. Februar 2024 um 22:55 UTC](https://meta.discourse.org/t/dashboard-report-topics-with-no-response/294002 "2024-02-02T22:55:48Z")\
**Posts on this page:** 1\
**Showing post:** 6

<div class="post-metadata">

**Author:** ![SaraDev](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/saradev/32/335139_2.png) [@SaraDev](https://meta.discourse.org/u/SaraDev)\
**Post date:** [3. September 2024 um 19:38 UTC](https://meta.discourse.org/t/dashboard-report-topics-with-no-response/294002/6 "2024-09-03T19:38:00Z")

</div>

Hallo Sophie,

> [@SStrong](#):
>
> Wie könnte man diesen Code so ändern, dass er nicht tagesaktuell nach Themen ohne Antwort sucht, sondern nach Jahr, Monat usw.?

Um die Abfrage so zu modifizieren, dass sie Themen ohne Antworten nach Jahr, Monat oder anderen Zeitintervallen aggregieren kann, könnten Sie der Funktion `date_trunc` einen Parameter hinzufügen, um das gewünschte Intervall anzugeben.

Zum Beispiel:

```sql
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- null int :category_id
-- boolean :include_subcategories = false
-- text :interval = day -- Optionen: day, week, month, year

WITH no_response_total AS (
  SELECT *
  FROM (
    SELECT t.id, t.created_at, MIN(p.post_number) first_reply
    FROM topics t
    LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
    WHERE t.archetype <> 'private_message'
      AND t.deleted_at ISNULL
      AND (
        :category_id IS NULL
        OR t.category_id = :category_id
        OR (
          :include_subcategories
          AND t.category_id IN (
            SELECT id FROM categories WHERE parent_category_id = :category_id
          )
        )
      )
    GROUP BY t.id
  ) tt
  WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)

SELECT
  date_trunc(:interval, nrt.created_at)::date AS period,
  COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN :start_date AND :end_date
GROUP BY period
ORDER BY period ASC

```

Wenn Sie die Parameter entfernen möchten, könnten Sie alternativ eine Abfrage wie diese verwenden:

```sql
WITH no_response_total AS (
  SELECT *
  FROM (
    SELECT t.id, t.created_at, MIN(p.post_number) first_reply
    FROM topics t
    LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
    WHERE t.archetype <> 'private_message'
      AND t.deleted_at ISNULL
    GROUP BY t.id
  ) tt
  WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)

SELECT
  date_trunc('year', nrt.created_at)::date AS period,
  COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN '2024-01-01' AND '2025-01-01'
GROUP BY period
ORDER BY period ASC

```

---

_[View the full topic](https://meta.discourse.org/t/dashboard-report-topics-with-no-response/294002)._