The discussion titled “User Page Metrics” involves retrieving user engagement metrics, such as likes received, likes given, topics viewed, posts read, days visited, solutions, and cheers, for a specific date range. srinivas.chilukuri initially requested a SQL query to achieve this, and SaraDev provided a query using the directory_items table. However, srinivas.chilukuri required a query with start and end dates, which led to SaraDev providing an updated query using separate Common Table Expressions (CTEs) for each metric.
The updated query uses the user_actions, user_stats, user_visits, topic_views, and gamification_scores tables to retrieve the required metrics. However, srinivas.chilukuri noticed discrepancies in the results, particularly for topics_entered, posts_read_count, and days_visited. SaraDev explained that the discrepancies stem from using the user_stats table instead of other tables like user_visits and topic_views to filter statistics by date.
To address this, SaraDev provided an updated query that uses the correct tables to filter metrics by date. The query includes CTEs for likes received, likes given, user metrics, solutions, and cheers, and joins these CTEs with the users table to retrieve the required metrics. Note that the posts_read data in the user_visits table does not count a user’s own posts, which may still cause differences between the query results and the User Page statistics. Jagster also encountered an error with the query, but it was not directly related to the main discussion.
@SaraDev
can u provide the sql query for metrics available at https://meta.discourse.org/u?cards=no&order=post_count
refer image below
metrics
we are utilizing category moderators hence we are modifying the moderator query to any given group
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:
1: all time2: yearly3: monthly4: weekly5: daily6: quarterlyExample Results for this report would look like:
| user | likes_received | likes_given | topics_viewed | topic_count | post_count | days_visited | posts_read | solutions | cheers |
|---|---|---|---|---|---|---|---|---|---|
| Username1 | 4 | 17 | 250 | 69 | 116 | 480 | 217 | 10 | 844100 |
| Username2 | 2 | 5 | 47 | 0 | 2 | 43 | 59 | 1 | 112305 |
| Username3 | 0 | 4 | 2 | 0 | 0 | 2 | 7 | 0 | 3100 |
| … | … | .. | … | … | … | … | … | … | … |
@SaraDev
I require the start and end date . is there a workaround for getting the given metrics with start and end date
Note : I am getting metrics for a small subset of total users
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:
:start_date and :end_date are parameters that define the date range for the data being queried.action_type = 2) within the specified date range.action_type = 1) within the specified date range.action_type = 15) within the specified date range.LEFT JOIN to ensure all users are included, even if they have no activity in some categories, filling in with zeros using COALESCE.Example Results
| user_id | likes_received | likes_given | topics_viewed | posts_read | days_visited | solutions | cheers |
|---|---|---|---|---|---|---|---|
| 1 | 10 | 5 | 20 | 100 | 15 | 2 | 30 |
| 2 | 0 | 3 | 5 | 20 | 5 | 0 | 10 |
| — | — | — | — | — | — | — | — |
We used the query provided in the post above and have the following questions:
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).Key Discrepancies:
topics_enteredposts_read_countdays_visitedCould you clarify if there’s a better way to retrieve time-bound user metrics?
PG::UndefinedColumn: ERROR: column uv.topic_id does not exist
LINE 38: COUNT(DISTINCT uv.topic_id) AS topics_viewed, -- Cou...
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.