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

[Sean_R (Republic Wireless)]

We are allowing our Trust Level 3 customers to mark Solved, but we have no good way to see how many they are doing. For example, see if a small number of this group are actually doing this work.

I did see

See who marked post as solved?

Is there a way to see who exactly marked the post as “solved”? Thanks!

… and I do want that as well.

Could I use data Explorer? I am looking for total accepted Solution over a time period by a user.

From date to date
Name | Total
Mike | 8
Jim | 5
Tim | 3
Tom | 2
Jessica | 1
Beth | 0

Staff would be on the list and the OP would as well (nice to have would be listed as OP)

[codinghorror (Jeff Atwood)]

Not sure, any ideas @sam?

[sam (Sam Saffron)]

I have this bookmarked it is quite easy to add a query to data explorer

[david (David Taylor)]

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

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.

[Sean_R (Republic Wireless)]

– [params]
– string :interval = 1 year
SELECT ua.user_id,
count(case t.user_id when ua.user_id then 1 else null end) as is_op,
count(case t.user_id when ua.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.user_id
ORDER BY total DESC

Do these show the user with the correct answer? I am looking for more of a log as to who is doing the marking helps me keep people honest.

I do like the queries, however

[david (David Taylor)]

Yep, you’re quite right. I just updated my post with a corrected query. Also note the cavet I put at the bottom

[Sean_R (Republic Wireless)]

Awesome! Thanks!

[Sean_R (Republic Wireless)]

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

Any way to make this show the user that marked it solved? It shows the user that solved it and their post, very useful. It would be nice to see who did the marking in this also.

[david (David Taylor)]

I did another sneaky edit there just now, if you copy/paste it again then it should work as intended - let me know if not!

I really should test things better before posting

[Sean_R (Republic Wireless)]

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

So sneaky! Looks great!!

[jerdog (Jeremy M)]

So looking at this, if I read this correctly, “is_op” means their post has been marked as a solution to a topic? I am building a Monthly Leaderboard query and want to add those who’ve had posts marked as solutions to topics.