Dashboard Report - Topic View Stats

[SaraDev (Sara Devlaeminck)]

This is an SQL version of the Dashboard Report for Topic View Stats.

This report collects and aggregates topic view statistics, including anonymous and logged-in views, within a given date range. It also allows filtering by categories and optionally includes subcategories.

This query and thetopic_view_stats database table was added to Discourse in May 2024, and will not show any data prior to this.

-- [params]
-- date :start_date
-- date :end_date
-- int :category_id = 0
-- boolean :include_subcategories = false

WITH category_filter AS (
  SELECT
    id
  FROM categories
  WHERE
    (:category_id = 0 OR id = :category_id)
    OR (:include_subcategories AND parent_category_id = :category_id)
)

SELECT
  topic_view_stats.topic_id,
  topics.title AS topic_title,
  SUM(topic_view_stats.anonymous_views) AS total_anonymous_views,
  SUM(topic_view_stats.logged_in_views) AS total_logged_in_views,
  SUM(topic_view_stats.anonymous_views + topic_view_stats.logged_in_views) AS total_views
FROM topic_view_stats
INNER JOIN topics ON topics.id = topic_view_stats.topic_id
WHERE
  topic_view_stats.viewed_at BETWEEN :start_date AND :end_date
  AND (
    :category_id = 0
    OR topics.category_id IN (SELECT id FROM category_filter)
  )
GROUP BY topic_view_stats.topic_id, topics.title
ORDER BY total_views DESC
LIMIT 100

SQL Query Explanation

Parameters

• :start_date (date): The beginning date of the range for the analysis (inclusive).
• :end_date (date): The ending date of the range for the analysis (inclusive).
• :category_id (integer, optional): Filter topics by category. Use:
  • 0 to include all categories.
  • Any specific category ID to filter by that category.
• :include_subcategories (boolean, optional): Choose whether to include subcategories of the specified category.
  • false: Only the selected category.
  • true: Also include subcategories.

Category filtering
The category_filter Common Table Expression (CTE) retrieves the selected category ID(s) based on the :category_id parameter. If the parameter is 0, all categories are included. If subcategories are to be included, it finds categories where the parent_category_id matches the specified :category_id.

Retrieve topic stats
Topic view statistics are aggregated from the topic_view_stats table, joined with the topics table to retrieve topic titles.

Apply date and category filters
The query filters the topic_view_stats data:

• By the provided date range (:start_date and :end_date).
• By category, matching against the IDs in the category_filter.

Calculate total views
The query sums up:

• anonymous_views: Views from anonymous users.
• logged_in_views: Views from logged-in users.
• Total views as the sum of both.

Ranking and limiting:
The query ranks topics by total_views and limits the result to the top 100 topics.

Query Results
The query returns the following columns:

Column Name	Description
topic_id	The unique identifier for the topic.
topic_title	The title of the topic.
total_anonymous_views	The total views from anonymous users.
total_logged_in_views	The total views from logged-in users.
total_views	The total views (anonymous + logged-in).

Example Results

topic_id	topic_title	total_anonymous_views	total_logged_in_views	total_views
12345	“Example Topic 1”	500	300	800
67890	“Example Topic 2”	450	350	800