[תמונה] User posts by email domain over last 7 days Data & reporting I think it actually gives inflated numbers due to the group joins (also, everyone is part of an automatic group for their trust level, so there’s a bit of inconsistency there). I think we can strip out those and maybe add a couple of tweaks to zone in topics, posts, and PMs. Let me have a bash. Maybe something like this: -- [params] -- string :interval = 7 days SELECT split_part(ue.email, '@', 2) AS email_domain, -- Extracts the domain part of the email COUNT(p.id) AS "Total Posts", … 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.

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:

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

סטטיסטיקות משתמש ועוד אימейל

קהילה נתונים ודיווח

Heather13 (Heather D) 24 ביולי,‏ 2024,‏ 10:55am 4

This is perfect!! Thank you so very much.

נושא		תגובות	צפיות
User posts by email domain over last 7 days Data & reporting sql-query	3	373	19 באפריל,‏ 2024
User participation Data & reporting sql-query	0	236	17 בנובמבר,‏ 2017
Daily, weekly, or total stats by user over a specified time range Data & reporting sql-query	0	264	31 בדצמבר,‏ 2019
Posts Read (Daily) Data & reporting sql-query	0	158	1 במאי,‏ 2016
Data explorer query for all active users (Lurkers + Posters) Data & reporting sql-query	5	1126	25 בנובמבר,‏ 2024

סטטיסטיקות משתמש ועוד אימейל

נושאים קשורים