How do I see who is marking solved and on what?

data & reporting
,
4

I was interested to see these stats for my instance as well, so had a go at making some queries for data explorer:

To list each individual “solved” event:

SELECT acting_user_id, target_topic_id, target_post_id, created_at FROM user_actions
WHERE action_type=15
ORDER BY created_at DESC

To produce a list with stats per-user: (with variable for time interval, defaults to 1 year):

-- [params]
-- string :interval = 1 year
SELECT  ua.acting_user_id, 
        count(case t.user_id when ua.acting_user_id then 1 else null end) as is_op, 
        count(case t.user_id when ua.acting_user_id then null else 1 end) as not_op,
        count(*) as total
FROM user_actions ua
LEFT JOIN topics t ON target_topic_id = t.id
WHERE action_type=15
AND ua.created_at >= CURRENT_DATE - INTERVAL :interval
GROUP BY ua.acting_user_id
ORDER BY total DESC

Screen Shot 2017-06-06 at 00.26.56.png896×279 6.43 KB

Edit: actually, this data is somewhat wrong… I’ll attempt to fix it
Edit 2: Since this logging was only added to the plugin 6 months ago, any data before that will have the “acting_user” set to the OP of the topic, regardless of who actually clicked the button. I think anything less than 6 months old should be correct.

12 Likes