# Dashboard Report - Flags

**URL:** https://meta.discourse.org/t/dashboard-report-flags/289412
**Category:** Data & reporting
**Tags:** sql-query, dashboard-reports, dashboard-sql
**Created:** [December 21, 2023, 4:19am UTC](https://meta.discourse.org/t/dashboard-report-flags/289412 "2023-12-21T04:19:04Z")
**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 21, 2023, 4:19am UTC](https://meta.discourse.org/t/dashboard-report-flags/289412/1 "2023-12-21T04:19:05Z")

</div>

This is an SQL version of the Dashboard Report for Flags

This report provides a count of flagged posts within an optional specific category (and optionally its subcategories) over a given date range. This information can be useful for community managers and moderators to understand the volume of flags raised in certain parts of the forum, which can help in identifying trends and areas that may require additional attention or moderation.

```sql
-- [params]
-- date :start_date = 2023-01-01
-- date :end_date = 2024-01-01
-- null int :category_id = 0
-- boolean :include_subcategories = false

WITH RECURSIVE subcategories AS (
    SELECT id FROM categories WHERE id = :category_id
    UNION ALL
    SELECT c.id FROM categories c
    INNER JOIN subcategories sc ON c.parent_category_id = sc.id
)

SELECT
    DATE(r.created_at) AS flag_date,
    COUNT(r.id) AS flags_count
FROM reviewables r
LEFT JOIN categories c ON r.category_id = c.id
WHERE r.type = 'ReviewableFlaggedPost'
    AND r.created_at::DATE BETWEEN :start_date AND :end_date
    AND (:category_id = 0 OR (
        :include_subcategories
        AND r.category_id IN (SELECT id FROM subcategories)
        OR NOT :include_subcategories
        AND r.category_id = :category_id
    ))
GROUP BY flag_date
ORDER BY flag_date

```

### SQL Query Explanation

The SQL query is designed to count the number of flags (which are represented by `ReviewableFlaggedPost` records in the `reviewables` table) created on each date within the specified date range and category. It uses a recursive Common Table Expression (CTE) to optionally include subcategories of the chosen category.

#### Parameters

- `:start_date`: The beginning of the date range for which to count flags.
- `:end_date`: The end of the date range for which to count flags.
- `:category_id`: The ID of the category for which to count flags. If set to 0, the filter on category is not applied.
- `:include_subcategories`: A boolean value that determines whether to include subcategories of the specified category in the count.

#### Query Breakdown

1. **Recursive CTE (`subcategories`)**:

- This CTE starts by selecting the category with the ID provided by the `:category_id` parameter.
- It then recursively selects all subcategories of that category by joining the `categories` table with the `subcategories` CTE on the `parent_category_id` field.
- The result is a list of all subcategories (including the initial category) that will be used for filtering.

1. **Main Query** :

- The query selects two columns: the date of flag creation (`flag_date`) and the count of flags (`flags_count`) for that date.
- It joins the `reviewables` table with the `categories` table to associate each flag with its category.

1. **Filters** :

- The query filters `reviewables` records to only include those of type `ReviewableFlaggedPost`, which represents flagged posts.
- It ensures that the `created_at` date of the flags falls within the specified date range (`:start_date` to `:end_date`).
- It applies a conditional filter based on the `:category_id` and `:include_subcategories` parameters:
  - If `:category_id` is 0, no category filter is applied.
  - If `:include_subcategories` is `true`, the query includes flags from the initial category and all its subcategories.
  - If `:include_subcategories` is `false`, the query includes flags only from the initial category.

1. **Grouping and Ordering** :

- The results are grouped by the date of flag creation to provide a count per day.
- The results are ordered by the date to present the data in a chronological sequence.

### Example Results

| flag\_date | flags\_count |
| --- | --- |
| 2023-11-19 | 2 |
| 2023-11-22 | 1 |
| 2023-11-23 | 1 |
| 2023-11-24 | 2 |
| 2023-11-26 | 1 |
| … | … |