# Dashboard Report - Posts

**URL:** <https://meta.discourse.org/t/dashboard-report-posts/288579>\
**Category:** Data & reporting\
**Tags:** sql-query, dashboard-reports, dashboard-sql\
**Created:** [December 13, 2023, 10:23pm UTC](https://meta.discourse.org/t/dashboard-report-posts/288579 "2023-12-13T22:23:09Z")\
**Posts on this page:** 1\
**Showing post:** 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 13, 2023, 10:23pm UTC](https://meta.discourse.org/t/dashboard-report-posts/288579/1 "2023-12-13T22:23:09Z")

</div>

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

This report provides a daily count of posts created within a specified date range. It is designed to track the activity in regular topics, excluding posts from private messages and other special archetypes.

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

```

### SQL Query Explanation

- **Parameters** :
  - The query accepts two parameters, `:start_date` and `:end_date`, which define the date range for the report. Both date parameters accept the date format of `YYYY-MM-DD`.

The SQL query performs the following operations:

- **Data Selection** :
  - It selects the date (`created_at::date`) when each post was created and casts it to a date format to ignore the time component.
  - It also counts the number of posts (`COUNT(p.id)`) created on each date.

- **Joins** :
  - The query joins the `posts` table with the `topics` table using an `INNER JOIN`. This join ensures that only posts associated with existing topics are considered.
  - It filters out any topics that have been deleted (`t.deleted_at ISNULL`).

- **Filters** :
  - It filters posts to include only those within the specified date range (`p.created_at::date BETWEEN :start_date AND :end_date`).
  - It excludes deleted posts (`p.deleted_at ISNULL`).
  - It restricts the results to posts from regular topics (`t.archetype = 'regular'`).
  - It considers only `p.post_type = 1` posts, excluding moderator actions, whispers, and small\_action posts.

- **Grouping and Ordering** :
  - The results are grouped by the date of post creation (`GROUP BY p.created_at::date`).
  - The final output is ordered by the date in ascending order (`ORDER BY 1`), where `1` refers to the first column in the `SELECT` statement, which is the date.

### Example Results

| Day | Count |
| --- | --- |
| 2023-11-12 | 25 |
| 2023-11-13 | 35 |
| 2023-11-14 | 38 |
| 2023-11-15 | 47 |
| 2023-11-16 | 36 |
| 2023-11-17 | 79 |
| … | … |

---

_[View the full topic](https://meta.discourse.org/t/dashboard-report-posts/288579)._