# Dashboard Report - Topics

**URL:** https://meta.discourse.org/t/dashboard-report-topics/288446
**Category:** Data & reporting
**Tags:** sql-query, dashboard-reports, dashboard-sql
**Created:** [December 12, 2023, 11:25pm UTC](https://meta.discourse.org/t/dashboard-report-topics/288446 "2023-12-12T23:25:03Z")
**Posts on this page:** 1
**Page:** 1

<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: [December 12, 2023, 11:25pm UTC](https://meta.discourse.org/t/dashboard-report-topics/288446/1 "2023-12-12T23:25:03Z")

</div>

This is an SQL version of the Dashboard Report for Topics.

This report provides a count of new topics created on a daily basis within a specified date range. The report does not include personal messages, only regular topics that are visible on the forum.

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

SELECT 
     p.created_at::date as day,
    COUNT(p.id) AS topics_created
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.post_number = 1
    AND p.user_id > 0 
    AND p.deleted_at ISNULL
    AND t.archetype = 'regular'
GROUP BY p.created_at::date
ORDER BY p.created_at::date ASC

```

### SQL Query Explanation

The SQL query performs the following actions:

- **Parameter Definition** : It accepts two parameters, `:start_date` and `:end_date`, which define the date range for the report. Both date parameters accept the format of `YYYY-MM-DD`.
- **Data Selection** : The query selects the creation date of posts (`p.created_at`) and the count of post IDs (`COUNT(p.id)`) as `topics_created`.
- **Joins** : It joins the `posts` table with the `topics` table on their respective `id` fields, ensuring that only non-deleted topics (`t.deleted_at ISNULL`) are considered.
- **Filters** : The query filters posts to include only those that:
  - Were created within the specified date range (`p.created_at::date BETWEEN :start_date AND :end_date`).
  - Are the first post in a topic (`p.post_number = 1`), indicating the start of a new topic.
  - Were created by registered users (`p.user_id > 0`), excluding system posts.
  - Have not been deleted (`p.deleted_at ISNULL`).
  - Belong to topics of the ‘regular’ archetype (`t.archetype = 'regular'`), excluding personal messages.

- **Grouping** : The results are grouped by the creation date of the posts (`p.created_at::date`).
- **Ordering** : The final output is ordered by the creation date in ascending order (`ORDER BY p.created_at::date ASC`), providing a chronological view of new topic creation.

### Example Results

| day | topics\_created |
| --- | --- |
| 2023-11-11 | 18 |
| 2023-11-12 | 10 |
| 2023-11-13 | 21 |
| 2023-11-14 | 19 |
| 2023-11-15 | 22 |
| … | … |