Having in mind that we have Discourse Solved plugin installed as well as the Data Explorer plugin, 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:
SELECT
user_id,
COUNT(1) AS solved_count
FROM user_actions
WHERE action_type = 15
GROUP BY user_id
ORDER BY solved_count DESC
9 „Gefällt mir“
That works! Thanks a lot!
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!
You can’t mark PMs as solved. It’s a per category setting.
2 „Gefällt mir“
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.
3 „Gefällt mir“
Thanks a lot Simon for that knowledge!
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):
--[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.
5 „Gefällt mir“
Thanks Ben for sharing that!
1 „Gefällt mir“
Danke an alle für das Teilen dieser Abfragen!
Ich richte mein Discourse-Forum ein und frage mich, wie man die durchschnittliche Zeit bis zur Lösung (time_to_resolution) für das gesamte Forum ermitteln kann, ähnlich wie bei der average_time_for_first_response.
Meinst du, dass es aus Community-Sicht sinnvoll ist, solche Informationen bereitzustellen? Falls ja, bin ich mir nicht sicher, wie man diese Abfrage formulieren soll. Hast du Ideen?
Vielen Dank!