Data Explorer query to identify trust level change dates

Community Building Data & reporting

AI-generated summary

In a discussion about tracking when users reach Trust Level 3 (TL3) using Data Explorer, Randy_Hulett asked if the database logs trust level change dates. simon confirmed that the group_users.created_at field records when a user was added to a group (e.g., trust_level_3) and provided a sample SQL query using parameters :group_name and :granted_at to find users added since a given date. Randy_Hulett confirmed the solution worked. Later, Shirly_Nowak asked if the query was publicly available and if it could be adapted for TL2 as well. Randy_Hulett replied that by joining groups and group_users (as shown by simon), one can filter for trust_level_3 (or trust_level_2) and optionally exclude admins with u.admin = false. The discussion highlights using SQL in Data Explorer to monitor trust level milestones for user engagement.

1

We would like to welcome users when they achieve trust level 3. (We would monitor this periodically through a Data Explorer query.)

Does the database contain information on when a user’s trust level changes?
Or only what the user’s current trust level is?

Thanks,
Randy

2

Yes, the group_users created_at field can be used for that. It will be set to the date when the user was added to the group. You could try running a query like the one below at periodic intervals to find all users who were added to the group during the interval. The query’s granted_at field expects a date in the form yyyy-mm-dd. For example 2020-09-15

--[params]
-- string :group_name = trust_level_3
-- date :granted_at

SELECT
user_id,
gu.created_at::date
FROM group_users gu
JOIN groups g
ON g.id = gu.group_id
WHERE gu.created_at::date >= :granted_at
AND g.name = :group_name

If you need more user details, it would be possible to update the query to join the users table, or the user_emails table.

3

This works perfectly, @simon.
Thanks so much!!!

4

Hi @Randy_Hulett , is this query available somewhere? I am looking to monitor users that achieve TLs 2 and 3 but don’t seem to find the query for that.

5

If you join on the groups and group_users tables as @simon showed, you can add something like this to your WHERE clause:

WHERE gu.created_at::date >= :since
    AND g.name = 'trust_level_3'
    AND u.admin = false