Dashboard Report - Flags

data & reporting
, ,
1

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.

-- [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
… …
1 Like