# 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:** 3

<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: [September 9, 2025, 7:34pm UTC](https://meta.discourse.org/t/dashboard-report-posts/288579/3 "2025-09-09T19:34:18Z")

</div>

> [@tknospdr](#):
>
> Would there be a way to be able to define the category/subcategory at run time as the date?  
> And bonus, can we either list the results by user, or define the user as well?

Yes, you can use the following query for this:

```sql
--[params]
-- date :start_date
-- date :end_date
-- null category_id :category_id 
-- null user_id :user_id
-- boolean :include_subcategories = false

SELECT 
    u.username AS "User",
    p.created_at::date AS "Date",
    COUNT(p.id) AS "Count"
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id AND t.deleted_at IS NULL
INNER JOIN users u ON p.user_id = u.id
LEFT JOIN categories c ON t.category_id = c.id
WHERE p.created_at::date BETWEEN :start_date AND :end_date
    AND p.deleted_at IS NULL
    AND t.archetype = 'regular'
    AND p.post_type = 1
    AND (
        :category_id IS NULL 
        OR t.category_id = :category_id
        OR (:include_subcategories AND c.parent_category_id = :category_id)
    )
    AND (:user_id IS NULL OR p.user_id = :user_id)
GROUP BY u.username, p.created_at::date
ORDER BY p.created_at::date ASC, u.username

```

Parameters:

- **:start\_date & :end\_date** : Define the reporting timeframe (required)
- **:category\_id** : Optional filter for a specific category
- **:user\_id** : Optional filter for a specific user
- **:include\_subcategories** : Option to include subcategories of the chosen category

This query shows:

- **User** : Username of the post author
- **Date** : The calendar date when posts were created
- **Count** : Number of posts created by that user on that date

Example Data:

| User | Date | Count |
| --- | --- | --- |
| user 1 | 2023-01-01 | 3 |
| user 2 | 2023-01-01 | 2 |
| user 3 | 2023-01-01 | 1 |
| user 1 | 2023-01-02 | 2 |
| user 2 | 2023-01-02 | 3 |
| user 1 | 2023-01-03 | 1 |
| … | … | … |

---

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