信頼レベルの変更日を特定するためのData Explorerクエリ

データとレポート
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

「いいね！」 3
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.

「いいね！」 4
3

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

「いいね！」 3
4

@Randy_Hulett さん、このクエリはどこかで利用可能ですか？TL2とTL3を達成したユーザーを監視したいのですが、それに対応するクエリが見つかりません。

5

groups テーブルと group_users テーブルを @simon が示したように結合すると、WHERE 句に次のようなものを追加できます。

WHERE gu.created_at::date >= :since
    AND g.name = 'trust_level_3'
    AND u.admin = false
「いいね！」 3