Dashboard Report - Topic View Stats

Data & reporting
, ,
1

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.

:discourse: 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
3 Likes