Polling specific users for their activity

[Jim.Morrison]

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!

[simon]

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

[Jim.Morrison]

This is perfect! Thanks @simon!