Metriche della pagina utente

Dati e reportistica
8

È corretto che la tabella user_stats sia una tabella statica che riassume le metriche cumulative di un utente da quando si è unito a Discourse.

Invece, per filtrare le metriche per data come posts_read_count e days_visited, dovremmo usare la tabella del database user_visits per posts. Dovremmo anche usare la tabella topic_views per filtrare le metriche topics_entered per data.

Le discrepanze che hai osservato derivano dall’uso della tabella user_stats invece di altre tabelle come user_visits e topic_views per filtrare tali statistiche per data.

Per risolvere questo problema, possiamo aggiornare la query per utilizzare invece quelle tabelle del database:

Ecco una versione aggiornata della query:

Metriche della pagina utente

-- [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

Nota che con questo metodo, i dati posts_read nella tabella user_visits hanno una distinzione importante: non contano i post dell’utente stesso, mentre i dati della tabella user_stats includono i post scritti dall’utente, quindi potresti comunque riscontrare una differenza tra queste due statistiche in questa query e nella Pagina Utente.

1 Mi Piace