# User Page Metrics
**URL:** https://meta.discourse.org/t/user-page-metrics/354906
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [October 31, 2024, 12:52am UTC](https://meta.discourse.org/t/user-page-metrics/354906 "2024-10-31T00:52:40Z")
**Posts on this page:** 7
**Page:** 1
### Author:  [@srinivas.chilukuri](https://meta.discourse.org/u/srinivas.chilukuri)
#### Post date: [October 31, 2024, 12:52am UTC](https://meta.discourse.org/t/user-page-metrics/354906/1 "2024-10-31T00:52:40Z")
@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
- like received
- like given
- topics viewed
- posts read
- days visited
- solutions
- cheers
* * *
we are utilizing category moderators hence we are modifying the [moderator query](https://meta.discourse.org/t/dashboard-report-moderator-activity/291244) to any given group
---
### Author:  [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### Post date: [October 31, 2024, 7:54pm UTC](https://meta.discourse.org/t/user-page-metrics/354906/2 "2024-10-31T19:54:37Z")
Hi @srinivas.chilukuri,
The `/u` user page statistics can be retrieved via the [Data Explorer](https://meta.discourse.org/t/32566?silent=true) by using the `directory_items` table.
**User Directory Page Metrics**
```sql
-- [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 time
- `2`: yearly
- `3`: monthly
- `4`: weekly
- `5`: daily
- `6`: quarterly
Example 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 |
| … | … | .. | … | … | … | … | … | … | … |
---
### Author:  [@srinivas.chilukuri](https://meta.discourse.org/u/srinivas.chilukuri)
#### Post date: [October 31, 2024, 11:35pm UTC](https://meta.discourse.org/t/user-page-metrics/354906/3 "2024-10-31T23:35:15Z")
@SaraDev
I require the start and end date . is there a workaround for getting the given metrics with start and end date
- user
- likes\_received
- likes\_given
- topics\_viewed
- topic\_count
- post\_count
- days\_visited
- posts\_read
- solutions
- cheers
Note : I am getting metrics for a small subset of total users
---
### Author:  [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### Post date: [November 1, 2024, 8:23pm UTC](https://meta.discourse.org/t/user-page-metrics/354906/4 "2024-11-01T20:23:26Z")
> [@srinivas.chilukuri](#):
>
> I require the start and end date . is there a workaround for getting the given metrics with start and end date
>
> - user
> - likes\_received
> - likes\_given
> - topics\_viewed
> - topic\_count
> - post\_count
> - days\_visited
> - posts\_read
> - solutions
> - cheers
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**
```sql
-- [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:**
1. **Parameters** :
- `:start_date` and `:end_date` are parameters that define the date range for the data being queried.
1. **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.
1. **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`.
**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 |
| — | — | — | — | — | — | — | — |
---
### Author:  [@srinivas.chilukuri](https://meta.discourse.org/u/srinivas.chilukuri)
#### Post date: [February 27, 2025, 7:53am UTC](https://meta.discourse.org/t/user-page-metrics/354906/5 "2025-02-27T07:53:48Z")
**@SaraDev**
> [@SaraDev](#):
>
> User Metrics
We used the query provided in the post above and have the following questions:
1. 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).
2. **Time-Series Comparison (T/S C/O)**
For a given set of users, we compared the available time-period data on the [user page](https://discourse.onlinedegree.iitm.ac.in/u?order=likes_received&period=monthly) 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?
---
### Author:  [@Jagster](https://meta.discourse.org/u/Jagster)
#### Post date: [February 27, 2025, 9:01pm UTC](https://meta.discourse.org/t/user-page-metrics/354906/7 "2025-02-27T21:01:53Z")
```plaintext
PG::UndefinedColumn: ERROR: column uv.topic_id does not exist
LINE 38: COUNT(DISTINCT uv.topic_id) AS topics_viewed, -- Cou...
```
---
### Author:  [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### Post date: [February 27, 2025, 11:14pm UTC](https://meta.discourse.org/t/user-page-metrics/354906/8 "2025-02-27T23:14:40Z")
> [@Dashboard Report - Moderator Activity](https://meta.discourse.org/t/dashboard-report-moderator-activity/291244/11):
>
> 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).
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.
> [@Dashboard Report - Moderator Activity](https://meta.discourse.org/t/dashboard-report-moderator-activity/291244/11):
>
> For a given set of users, we compared the available time-period data on the [user page](https://discourse.onlinedegree.iitm.ac.in/u?order=likes_received&period=monthly) and noticed significant discrepancies.
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**
```sql
-- [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.