I found this topic when I was looking for a way to see how often someone is tagged/mentioned.
I have a product-driven forum with users that our forum likes to call on for advice/expertise/etc, but we don’t want to always bother the same people and would like to spread the love. Am I missing a way to keep track of who has been mentioned/tagged?
Mentions look like they crop up a couple of times in the tables, either in notifications or user_actions, so it may be possible to use those? It’s not a query I’ve considered before so this may have some holes in it But maybe something like this could give you a list of ‘most mentioned users by staff in the last month’? From there it could perhaps be tweaked to suit your specifics a bit more?
SELECT ua.user_id,
count(ua.user_id)
FROM user_actions ua
JOIN group_users gu ON gu.user_id = ua.acting_user_id
WHERE ua.action_type = 7
AND gu.group_id = 3
AND ua.created_at >= CURRENT_DATE - INTERVAL '1 MONTH'
GROUP BY ua.user_id
ORDER BY count(ua.user_id) DESC
LIMIT 100