# How do I see who is marking solved and on what?
**URL:** https://meta.discourse.org/t/how-do-i-see-who-is-marking-solved-and-on-what/63981
**Category:** Data & reporting
**Tags:** solved, sql-query
**Created:** [June 5, 2017, 7:34pm UTC](https://meta.discourse.org/t/how-do-i-see-who-is-marking-solved-and-on-what/63981 "2017-06-05T19:34:06Z")
**Posts on this page:** 12
**Page:** 1
### Author:  [@Sean\_R](https://meta.discourse.org/u/Sean_R)
#### Post date: [June 5, 2017, 7:34pm UTC](https://meta.discourse.org/t/how-do-i-see-who-is-marking-solved-and-on-what/63981/1 "2017-06-05T19:34:06Z")
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?](https://meta.discourse.org/t/see-who-marked-post-as-solved/37013):
>
> 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](https://meta.discourse.org/t/32566?silent=true)? 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)
---
### Author:  [@codinghorror](https://meta.discourse.org/u/codinghorror)
#### Post date: [June 5, 2017, 10:47pm UTC](https://meta.discourse.org/t/how-do-i-see-who-is-marking-solved-and-on-what/63981/2 "2017-06-05T22:47:05Z")
Not sure, any ideas @sam?
---
### Author:  [@sam](https://meta.discourse.org/u/sam)
#### Post date: [June 5, 2017, 10:51pm UTC](https://meta.discourse.org/t/how-do-i-see-who-is-marking-solved-and-on-what/63981/3 "2017-06-05T22:51:15Z")
I have this bookmarked it is quite easy to add a query to [data explorer](https://meta.discourse.org/t/32566?silent=true)
---
### Author:  [@david](https://meta.discourse.org/u/david)
#### Post date: [June 5, 2017, 11:31pm UTC](https://meta.discourse.org/t/how-do-i-see-who-is-marking-solved-and-on-what/63981/4 "2017-06-05T23:31:49Z")
I was interested to see these stats for my instance as well, so had a go at making some queries for [data explorer](https://meta.discourse.org/t/32566?silent=true):
To list each individual “solved” event:
```SQL
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):
```SQL
-- [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](https://github.com/discourse/discourse-solved/commit/026f7a3ab06150e3d4fdfca37ea7b9ea5e58d5f1), 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.
---
### Author:  [@Sean\_R](https://meta.discourse.org/u/Sean_R)
#### Post date: [June 5, 2017, 11:52pm UTC](https://meta.discourse.org/t/how-do-i-see-who-is-marking-solved-and-on-what/63981/5 "2017-06-05T23:52:18Z")
> [@david](#):
>
> – [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 🙂
---
### Author:  [@david](https://meta.discourse.org/u/david)
#### Post date: [June 5, 2017, 11:52pm UTC](https://meta.discourse.org/t/how-do-i-see-who-is-marking-solved-and-on-what/63981/6 "2017-06-05T23:52:57Z")
Yep, you’re quite right. I just updated my post with a corrected query. Also note the cavet I put at the bottom
---
### Author:  [@Sean\_R](https://meta.discourse.org/u/Sean_R)
#### Post date: [June 5, 2017, 11:54pm UTC](https://meta.discourse.org/t/how-do-i-see-who-is-marking-solved-and-on-what/63981/7 "2017-06-05T23:54:34Z")
Awesome! Thanks! 😀
---
### Author:  [@Sean\_R](https://meta.discourse.org/u/Sean_R)
#### Post date: [June 5, 2017, 11:59pm UTC](https://meta.discourse.org/t/how-do-i-see-who-is-marking-solved-and-on-what/63981/8 "2017-06-05T23:59:09Z")
> [@david](#):
>
> 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.
---
### Author:  [@david](https://meta.discourse.org/u/david)
#### Post date: [June 6, 2017, 12:01am UTC](https://meta.discourse.org/t/how-do-i-see-who-is-marking-solved-and-on-what/63981/9 "2017-06-06T00:01:11Z")
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 😆
---
### Author:  [@Sean\_R](https://meta.discourse.org/u/Sean_R)
#### Post date: [June 6, 2017, 12:05am UTC](https://meta.discourse.org/t/how-do-i-see-who-is-marking-solved-and-on-what/63981/10 "2017-06-06T00:05:07Z")
> [@david](#):
>
> 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!!
---
### Author:  [@jerdog](https://meta.discourse.org/u/jerdog)
#### Post date: [May 3, 2018, 3:15pm UTC](https://meta.discourse.org/t/how-do-i-see-who-is-marking-solved-and-on-what/63981/11 "2018-05-03T15:15:17Z")
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](https://meta.discourse.org/t/attempting-to-create-monthly-leaderboard-query-head-exploding/86679/) and want to add those who’ve had posts marked as solutions to topics.
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [June 8, 2024, 12:37pm UTC](https://meta.discourse.org/t/how-do-i-see-who-is-marking-solved-and-on-what/63981/12 "2024-06-08T12:37:30Z")
This topic was automatically closed after 2559 days. New replies are no longer allowed.