@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.