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

Community Building Data & reporting
,

AI-generated summary

Summary

The discussion revolves around finding a way to track who is marking posts as solved and on what topics in a Discourse forum. Sean_R initially asks if it’s possible to see the number of solved posts marked by each user over a certain period. david provides a solution using Data Explorer queries, which can list individual “solved” events and produce a list with stats per user. The queries were later corrected to accurately display the user who marked the post as solved.

david shared two SQL queries:

  • One to list each individual “solved” event, showing the user who solved it, the topic and post ID, and the date.
  • Another to produce a list with stats per user, including the total number of posts marked as solved, whether the user was the original poster (OP), and the number of non-OP posts marked as solved.

Sean_R and jerdog asked for further clarification and modifications to the queries, which david and david provided.

The discussion also highlights a caveat that data before 6 months ago may be incorrect due to changes in the plugin’s logging functionality.

1

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

… 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)

2

Not sure, any ideas @sam?

3

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

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

data
data896×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.

5

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 :slight_smile:

6

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

7

Awesome! Thanks! :grinning:

8

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.

9

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! :wink:

I really should test things better before posting :laughing:

10

So sneaky! Looks great!!

11

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.