Good afternoon, I would like to poll specific usernames for their likes, likes received, posts, solutions, for a specific date range broken up by days. Any suggestions on this type of reporting? I suspect I would need to leverage the data explorer plugin, does anyone have a query built out for this type of work? Any feedback is appreciated!

I had a query very similar to what you are looking for on my local dev site. I have modified it a bit to meet your requirements. One thing I’ve tried to do is allow the query to be run against either activity that has occurred in regular topics, or activity that has occurred in PMs. Part of my motivation for doing that was that I wanted to look at my own activity over a period of time. Being able to query by either regular topics or PMs makes sense for my case. The way solutions are handled by the query when the personal_messages checkbox is checked isn’t ideal - the solutions_provided column will just return NULL for that case. Possibly the regular-topic/PM switch should be removed from the query. This query isn’t overly complex, but it’s fairly long. If you notice any issues with its results, please let me know. The query requires you to supply three parameters before it can be run: username, start_date, and end_date. The date parameters need to be in the form ‘yyyy-mm-dd’. For example 2020-03-13. The personal_messages parameter defaults to false. Select the query’s personal_messages checkbox if you want to see results for activity that has occurred in PMs. --[params] -- string :username -- date :start_date -- date :end_date -- boolean :personal_messages = false WITH target_user AS (SELECT id FROM users WHERE username = :username), days AS ( SELECT day::date FROM GENERATE_SERIES(:start_date, :end_date, INTERVAL '1 day') AS day ), topics_created AS( SELECT tu.id, day, COUNT(tu.id) AS topics FROM topics t JOIN target_user tu ON tu.id = t.user_id JOIN days ON t.created_at::date = day WHERE t.archetype = CASE WHEN :personal_messages THEN 'private_message' ELSE 'regular' END AND t.deleted_at IS NULL GROUP BY day, tu.id ), posts_created AS( SELECT tu.id, day, COUNT(tu.id) AS posts FROM posts p JOIN target_user tu ON tu.id = p.user_id JOIN topics t ON t.id = p.topic_id JOIN days ON p.created_at::date = day WHERE p.post_type = 1 AND t.archetype = CASE WHEN :personal_messages THEN 'private_message' ELSE 'regular' END AND p.deleted_at IS NULL AND p.post_number > 1 GROUP BY day, tu.id ), likes_received AS ( SELECT tu.id, day, COUNT(tu.id) AS likes_received_count FROM user_actions ua JOIN target_user tu ON tu.id = ua.user_id JOIN topics t ON t.id = ua.target_topic_id JOIN days ON ua.created_at::date = day WHERE ua.action_type = 2 AND t.archetype = CASE WHEN :personal_messages THEN 'private_message' ELSE 'regular' END GROUP BY day, tu.id ), likes_given AS ( SELECT tu.id, day, COUNT(tu.id) AS likes_given_count FROM user_actions ua JOIN target_user tu ON tu.id = ua.user_id JOIN topics t ON t.id = ua.target_topic_id JOIN days ON ua.created_at::date = day WHERE ua.action_type = 1 AND t.archetype = CASE WHEN :personal_messages THEN 'private_message' ELSE 'regular' END GROUP BY day, tu.id ), solutions_provided AS ( SELECT tu.id, day, COUNT(tu.id) AS solutions_provided_count FROM user_actions ua JOIN target_user tu ON tu.id = ua.user_id JOIN days ON ua.created_at::date = day WHERE ua.action_type = 15 GROUP BY day, tu.id ) SELECT d.day, COALESCE(topics, 0) AS topics, COALESCE(posts, 0) AS posts, COALESCE(likes_received_count, 0) AS likes_received, COALESCE(likes_given_count, 0) AS likes_given, (SELECT CASE WHEN NOT :personal_messages THEN COALESCE(solutions_provided_count, 0) END) AS solutions_provided FROM days d LEFT JOIN topics_created tc ON tc.day = d.day LEFT JOIN posts_created pc ON pc.day = d.day LEFT JOIN likes_received lc ON lc.day = d.day LEFT JOIN likes_given lg ON lg.day = d.day LEFT JOIN solutions_provided sp ON sp.day = d.day ORDER BY d.day DESC

Polling specific users for their activity

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

Jim.Morrison (Jim Morrison) 16 במרץ,‏ 2020,‏ 9:19pm 3

This is perfect! Thanks @simon!

נושא		תגובות	צפיות
How to track user activity? Data & reporting sql-query	4	2796	22 ביוני,‏ 2021
PAID Data explorer challenge: chronological list of user topics started, replied and liked Marketplace sql-query	6	3482	4 באוגוסט,‏ 2016
Daily, weekly, or total stats by user over a specified time range Data & reporting sql-query	0	263	31 בדצמבר,‏ 2019
User participation Data & reporting sql-query	0	236	17 בנובמבר,‏ 2017
Dashboard Report - Daily Engaged Users Data & reporting sql-query , dashboard-reports , dashboard-sql	1	696	9 בדצמבר,‏ 2023

Polling specific users for their activity

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