Search for number of solved topics per user

Community Building Data & reporting

AI-generated summary

In the discussion “Search for number of solved topics per user”, lju is seeking help in building a Data Explorer query to return the number of topics with a post marked as a solution by users not in a given group, for a specific date range. JammyDodger provides a helpful link to a similar query and later offers a query example using the group_users table. The query example includes parameters for group_name, date_from, and date_to, and returns the number of solved topics per user. lju appreciates the help and notes the need for a data model in the future.

1

Hi All,

I am looking to build a query in the data explorer - and being completely new to it, am not sure where to start!

I would like to run the following query:
Return the number of topics with a post marked as a solution by folk not in a given group, for a start and end date.

I couldn’t see any similar queries that do this, so my apologies if I missed it. All hints and tips gratefully received!

Cheers,

Lju

2

Hello and welcome @lju :slight_smile:

This topic has a couple of examples in to get you started - How to get number of solutions per users using Data Explorer?

3

Hi @JammyDodger

Thanks for that - it was helpful, and I think I got my head around the various fields :slight_smile:

As a former data architect, I dream of having some sort of a data model to refer to, hint, hint…

Cheers,

Lju

5

I think you should be able to do it with two if you use group_users. Maybe something like:

-- [params]
-- string :group_name = staff
-- date :date_from = 01/04/2022
-- date :date_to = 01/05/2022


SELECT
gu.group_id,
ua.user_id,
COUNT(1) AS solved_count
FROM user_actions ua
JOIN group_users gu on ua.user_id = gu.user_id
WHERE ua.action_type = 15
AND gu.group_id = (SELECT id FROM groups WHERE name = :group_name)
AND ua.created_at::date BETWEEN :date_from::date AND :date_to::date
GROUP BY gu.group_id, ua.user_id
ORDER BY solved_count DESC

(FYI group_name is case sensitive)

Update: Belatedly realised I added in a third when I made the group look-up a bit more user-friendly than using a straight group_id. But it could be done with two. :slightly_smiling_face: