User Page Metrics

Community Building Data & reporting
4

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:

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