The /u user page statistics can be retrieved via the Data Explorer by using the directory_items table.
User Directory Page Metrics
-- [params]
-- int :period
-- Period Options:
-- 1. all
-- 2. yearly
-- 3. monthly
-- 4. weekly
-- 5. daily
-- 6. quarterly
SELECT
di.user_id,
COALESCE(di.likes_received, 0) AS likes_received,
COALESCE(di.likes_given, 0) AS likes_given,
COALESCE(di.topics_entered, 0) AS topics_viewed,
COALESCE(di.topic_count, 0) AS topic_count,
COALESCE(di.post_count, 0) AS post_count,
COALESCE(di.days_visited, 0) AS days_visited,
COALESCE(di.posts_read, 0) AS posts_read,
COALESCE(di.solutions, 0) AS solutions,
COALESCE(di.gamification_score, 0) AS cheers
FROM
directory_items di
WHERE
di.period_type = :period
ORDER BY
di.user_id
Instead of the typical start_date and end_date parameters, data from this table can be filtered using the period_type field, where the following values correspond to the different time periods available on the directory page:
If you want to view those metrics for users on your site and filter by specific start and end dates, you would need a query that pulls the data for each metric in a separate CTE, and then combine the results together in a final SELECT statement.
Here’s what that would look like:
User Metrics
-- [params]
-- date :start_date = 2020-01-01
-- date :end_date = 2025-01-01
WITH likes_received AS (
SELECT
ua.user_id AS user_id,
COUNT(*) AS likes_received
FROM
user_actions ua
WHERE
ua.action_type = 2
AND ua.created_at BETWEEN :start_date AND :end_date
GROUP BY
ua.user_id
),
likes_given AS (
SELECT
ua.acting_user_id AS user_id,
COUNT(*) AS likes_given
FROM
user_actions ua
WHERE
ua.action_type = 1
AND ua.created_at BETWEEN :start_date AND :end_date
GROUP BY
ua.acting_user_id
),
user_metrics AS (
SELECT
us.user_id,
SUM(us.topics_entered) AS topics_viewed,
SUM(us.posts_read_count) AS posts_read,
SUM(us.days_visited) AS days_visited
FROM
user_stats us
WHERE
us.first_post_created_at BETWEEN :start_date AND :end_date
GROUP BY
us.user_id
),
solutions AS (
SELECT
ua.acting_user_id AS user_id,
COUNT(*) AS solutions
FROM
user_actions ua
WHERE
ua.action_type = 15
AND ua.created_at BETWEEN :start_date AND :end_date
GROUP BY
ua.acting_user_id
),
cheers AS (
SELECT
gs.user_id,
SUM(gs.score) AS cheers
FROM
gamification_scores gs
WHERE
gs.date BETWEEN :start_date AND :end_date
GROUP BY
gs.user_id
)
SELECT
u.id AS user_id,
COALESCE(lr.likes_received, 0) AS likes_received,
COALESCE(lg.likes_given, 0) AS likes_given,
COALESCE(um.topics_viewed, 0) AS topics_viewed,
COALESCE(um.posts_read, 0) AS posts_read,
COALESCE(um.days_visited, 0) AS days_visited,
COALESCE(sol.solutions, 0) AS solutions,
COALESCE(ch.cheers, 0) AS cheers
FROM
users u
LEFT JOIN
likes_received lr ON u.id = lr.user_id
LEFT JOIN
likes_given lg ON u.id = lg.user_id
LEFT JOIN
user_metrics um ON u.id = um.user_id
LEFT JOIN
solutions sol ON u.id = sol.user_id
LEFT JOIN
cheers ch ON u.id = ch.user_id
ORDER BY
u.id
Query Explanation:
Parameters:
:start_date and :end_date are parameters that define the date range for the data being queried.
Common Table Expressions (CTEs):
likes_received: Counts the number of likes received by each user (action_type = 2) within the specified date range.
likes_given: Counts the number of likes given by each user (action_type = 1) within the specified date range.
user_metrics: Aggregates user statistics such as topics viewed, posts read, and days visited for users who made their first post within the specified date range.
solutions: Counts the number of solutions provided by each user (action_type = 15) within the specified date range.
cheers: Sums up the gamification scores for each user within the specified date range.
Final Selection:
The main query selects user engagement metrics for each user, including likes received, likes given, topics viewed, posts read, days visited, solutions provided, and cheers received.
It uses LEFT JOIN to ensure all users are included, even if they have no activity in some categories, filling in with zeros using COALESCE.
We used the query provided in the post above and have the following questions:
Is user_metrics from the user_stats table the correct source for this information? Given that user_stats is a static table summarizing a user’s metrics since they joined Discourse, it may not be ideal for filtering metrics within a specific time range (e.g., from a start date to an end date).
Time-Series Comparison (T/S C/O)
For a given set of users, we compared the available time-period data on the user page and noticed significant discrepancies.
Key Discrepancies:
topics_entered
posts_read_count
days_visited
Could you clarify if there’s a better way to retrieve time-bound user metrics?
You are correct that the user_stats table is a static table summarizing a user’s lifetime metrics since they joined Discourse.
Instead, for filter metrics by date like posts_read_count and days_visited, we would want to use the user_visits database table for posts. We would also want to use the topic_views table for filtering the topics_entered metrics by date.
The discrepancies you observed stem from using the user_stats table instead of other tables like user_visits and topic_views to filter those statistics by date.
To address this, we can update the query to use those database tables instead:
Here’s an updated version of the query:
User Page Metrics
-- [params]
-- date :start_date = 2020-01-01
-- date :end_date = 2026-01-01
WITH likes_received AS (
SELECT
ua.user_id AS user_id,
COUNT(*) AS likes_received
FROM
user_actions ua
WHERE
ua.action_type = 2
AND ua.created_at BETWEEN :start_date AND :end_date
GROUP BY
ua.user_id
),
likes_given AS (
SELECT
ua.acting_user_id AS user_id,
COUNT(*) AS likes_given
FROM
user_actions ua
WHERE
ua.action_type = 1
AND ua.created_at BETWEEN :start_date AND :end_date
GROUP BY
ua.acting_user_id
),
user_metrics AS (
SELECT
tv.user_id,
COUNT(DISTINCT tv.topic_id) AS topics_viewed
FROM
topic_views tv
WHERE
tv.viewed_at BETWEEN :start_date AND :end_date
GROUP BY
tv.user_id
),
days_and_posts AS (
SELECT
uv.user_id,
COUNT(DISTINCT uv.visited_at) AS days_visited,
SUM(uv.posts_read) AS posts_read
FROM
user_visits uv
WHERE
uv.visited_at BETWEEN :start_date AND :end_date
GROUP BY
uv.user_id
),
solutions AS (
SELECT
ua.acting_user_id AS user_id,
COUNT(*) AS solutions
FROM
user_actions ua
WHERE
ua.action_type = 15
AND ua.created_at BETWEEN :start_date AND :end_date
GROUP BY
ua.acting_user_id
),
cheers AS (
SELECT
gs.user_id,
SUM(gs.score) AS cheers
FROM
gamification_scores gs
WHERE
gs.date BETWEEN :start_date AND :end_date
GROUP BY
gs.user_id
)
SELECT
u.id AS user_id,
COALESCE(lr.likes_received, 0) AS likes_received,
COALESCE(lg.likes_given, 0) AS likes_given,
COALESCE(um.topics_viewed, 0) AS topics_viewed,
COALESCE(dp.days_visited, 0) AS days_visited,
COALESCE(dp.posts_read, 0) AS posts_read,
COALESCE(sol.solutions, 0) AS solutions,
COALESCE(ch.cheers, 0) AS cheers
FROM
users u
LEFT JOIN
likes_received lr ON u.id = lr.user_id
LEFT JOIN
likes_given lg ON u.id = lg.user_id
LEFT JOIN
user_metrics um ON u.id = um.user_id
LEFT JOIN
days_and_posts dp ON u.id = dp.user_id
LEFT JOIN
solutions sol ON u.id = sol.user_id
LEFT JOIN
cheers ch ON u.id = ch.user_id
ORDER BY
u.id
Note that with this method, the posts_read data in the user_visits table has one important distinction - it does not count a user’s own posts, while the data from the user_stats table does include self-authored posts, so you may still find a difference between these two statistics in this query and on the User Page.