# How to get number of solutions per users using Data Explorer?
**URL:** https://meta.discourse.org/t/how-to-get-number-of-solutions-per-users-using-data-explorer/146253
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [31 maart 2020 om 09:28 UTC](https://meta.discourse.org/t/how-to-get-number-of-solutions-per-users-using-data-explorer/146253 "2020-03-31T09:28:43Z")
**Posts on this page:** 11
**Page:** 1
### Author:  [@Konrad\_Sopala](https://meta.discourse.org/u/Konrad_Sopala)
#### Post date: [31 maart 2020 om 09:28 UTC](https://meta.discourse.org/t/how-to-get-number-of-solutions-per-users-using-data-explorer/146253/1 "2020-03-31T09:28:43Z")
Having in mind that we have **Discourse Solved** plugin installed as well as the [Data Explorer](https://meta.discourse.org/t/32566?silent=true) plugin, what would be the query to get the number of solutions per user?
---
### Author:  [@simon](https://meta.discourse.org/u/simon)
#### Post date: [31 maart 2020 om 17:01 UTC](https://meta.discourse.org/t/how-to-get-number-of-solutions-per-users-using-data-explorer/146253/2 "2020-03-31T17:01:24Z")
> [@Konrad\_Sopala](#):
>
> what would be the query to get the number of solutions per user?
When a user solves a topic, an entry is added to the `user_actions` table with its `action_type` set to `15`.
The following query should give you what you are looking for:
```sql
SELECT
user_id,
COUNT(1) AS solved_count
FROM user_actions
WHERE action_type = 15
GROUP BY user_id
ORDER BY solved_count DESC
```
---
### Author:  [@Konrad\_Sopala](https://meta.discourse.org/u/Konrad_Sopala)
#### Post date: [1 april 2020 om 11:34 UTC](https://meta.discourse.org/t/how-to-get-number-of-solutions-per-users-using-data-explorer/146253/3 "2020-04-01T11:34:58Z")
That works! Thanks a lot!
---
### Author:  [@Konrad\_Sopala](https://meta.discourse.org/u/Konrad_Sopala)
#### Post date: [7 mei 2020 om 12:46 UTC](https://meta.discourse.org/t/how-to-get-number-of-solutions-per-users-using-data-explorer/146253/4 "2020-05-07T12:46:17Z")
Hey there!
Got one more question regarding that. Does it count all the answers from public posts as well as those that the person marked as solution when exchanging private messages with someone? Thanks for help!
---
### Author:  [@HAWK](https://meta.discourse.org/u/HAWK)
#### Post date: [8 mei 2020 om 01:26 UTC](https://meta.discourse.org/t/how-to-get-number-of-solutions-per-users-using-data-explorer/146253/5 "2020-05-08T01:26:39Z")
> [@Konrad\_Sopala](#):
>
> Does it count all the answers from public posts as well as those that the person marked as solution when exchanging private messages with someone? Thanks for help!
You can’t mark PMs as solved. It’s a per category setting.
---
### Author:  [@Konrad\_Sopala](https://meta.discourse.org/u/Konrad_Sopala)
#### Post date: [11 mei 2020 om 07:49 UTC](https://meta.discourse.org/t/how-to-get-number-of-solutions-per-users-using-data-explorer/146253/6 "2020-05-11T07:49:41Z")
One of the replies in private messaging thread marked as a solution. Does that also counts for person’s solutions count?
---
### Author:  [@simon](https://meta.discourse.org/u/simon)
#### Post date: [11 mei 2020 om 22:14 UTC](https://meta.discourse.org/t/how-to-get-number-of-solutions-per-users-using-data-explorer/146253/8 "2020-05-11T22:14:37Z")
> [@Konrad\_Sopala](#):
>
> One of the replies in private messaging thread marked as a solution. Does that also counts for person’s solutions count?
If the `allow solved on all topics` site setting is enabled, private message posts can be marked as solutions. If a private message post is marked as a solution, it will be included in the solution counts that are returned by the query that I posted.
---
### Author:  [@Konrad\_Sopala](https://meta.discourse.org/u/Konrad_Sopala)
#### Post date: [12 mei 2020 om 07:00 UTC](https://meta.discourse.org/t/how-to-get-number-of-solutions-per-users-using-data-explorer/146253/9 "2020-05-12T07:00:51Z")
Thanks a lot Simon for that knowledge!
---
### Author:  [@BenLeong](https://meta.discourse.org/u/BenLeong)
#### Post date: [15 mei 2020 om 02:15 UTC](https://meta.discourse.org/t/how-to-get-number-of-solutions-per-users-using-data-explorer/146253/10 "2020-05-15T02:15:54Z")
As an addition to Simon’s query above, we use this version to view number of solutions per user for a specific date range (using `yyyy-mm-dd` date format):
```sql
--[params]
-- date :start_date
-- date :end_date
SELECT
user_id,
COUNT(1) AS solved_count
FROM user_actions
WHERE created_at::date BETWEEN :start_date AND :end_date
AND action_type = 15
GROUP BY user_id
ORDER BY solved_count DESC
```
It’s handy being able to see how this changes from month to month.
---
### Author:  [@Konrad\_Sopala](https://meta.discourse.org/u/Konrad_Sopala)
#### Post date: [15 mei 2020 om 12:38 UTC](https://meta.discourse.org/t/how-to-get-number-of-solutions-per-users-using-data-explorer/146253/11 "2020-05-15T12:38:10Z")
Thanks Ben for sharing that!
---
### Author:  [@OdysLam](https://meta.discourse.org/u/OdysLam)
#### Post date: [3 november 2020 om 15:16 UTC](https://meta.discourse.org/t/how-to-get-number-of-solutions-per-users-using-data-explorer/146253/12 "2020-11-03T15:16:09Z")
Thanks everyone for sharing those queries!
I am setting up my discourse forum and I was wondering how to get the average time\_to\_resolution of the whole forum, much like the average\_time-for\_first\_response.
Do you think it makes sense from a community perspective to have such information? If yes, I am not sure how to form that query. Any ideas?
Thanks!