User stats plus email

Community Building Data & reporting

AI-generated summary

The discussion titled “User stats plus email” revolves around creating a query to retrieve user statistics, including time read, posts read, and user visits. Heather13 shared a query and requested modifications to add the desired columns and define the interval parameter. JammyDodger suggested splitting the query into its own topic and provided an updated query that combines the required statistics. The updated query, shown in JammyDodger’s post, uses two Common Table Expressions (CTEs) to retrieve post data and user data, and then joins the results to provide the desired statistics. Heather13 confirmed that the updated query meets their requirements.

1

How would I add a column for time read, posts read, and user visits to this?

Essentially I’d like to combine the following query with the one you have posted here, plus add the time read (if possible):

select user_id,
    count(1) as visits,
    sum(posts_read) as posts_read
from user_visits
where posts_read > 0
and visited_at > CURRENT_TIMESTAMP - INTERVAL '7 days'
group by user_id
order by visits desc, posts_read desc

Note: I prefer the ability to define the interval parameter as you have in your query, but would either like to have the user from the query I posted be the full email (not just the email domain).

I am new at playing around with writing queries and understand the parameters and was able to modify yours to be the full email address. I just can’t seem to blend in the visits and posts_read columns from the query I shared, nor add the read time.

2

I think having the results per user rather than grouped by email domain makes this a slightly different query. Let’s split this off into its own topic. :+1:

3

I think something like this should do it:

-- [params]
-- string :interval = 7 days

WITH post_data AS (

    SELECT
        p.user_id,
        COUNT(p.id) AS total_posts,
        COUNT(p.id) FILTER (WHERE p.post_number = 1 AND t.archetype = 'regular') AS topics,
        COUNT(p.id) FILTER (WHERE p.post_number > 1 AND t.archetype = 'regular') AS posts,
        COUNT(p.id) FILTER (WHERE t.archetype = 'private_message') AS pms
    FROM posts p
      JOIN topics t ON t.id = p.topic_id
    WHERE p.created_at >= CURRENT_DATE - INTERVAL :interval 
      AND p.user_id > 0 
      AND p.deleted_at ISNULL
      AND t.deleted_at ISNULL
      AND p.post_type <> 3
GROUP BY p.user_id

    ),
    
user_data AS (
    
    SELECT 
        uv.user_id,
        COUNT(*) AS visits,
        SUM(posts_read) AS posts_read,
        SUM(time_read)/60 AS time_read
    FROM user_visits uv
    WHERE uv.visited_at >= CURRENT_DATE - INTERVAL :interval
    GROUP BY uv.user_id
    )

SELECT 
    ud.user_id,
    ue.email,
    ud.visits,
    ud.posts_read,
    ud.time_read,
    pd.total_posts,
    pd.topics,
    pd.posts,
    pd.pms
FROM user_data ud 
  LEFT JOIN post_data pd ON pd.user_id = ud.user_id
  JOIN user_emails ue ON ue.user_id = ud.user_id AND ue.primary = TRUE
ORDER BY ud.posts_read DESC
4

This is perfect!! Thank you so very much.