User Page Metrics

Data & reporting
8

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.

1 Like