Since our major goal was to provide this information to moderators, we used the badge system to accomplish this for our purposes.
Here is what we did:
- Created a badge that mirrored the category name
- Set the badge trigger to run daily
- Used this SQL for the badge query
select user_id, current_timestamp granted_at from category_users
where notification_level = 3
and category_id = 15
select id, current_timestamp granted_at from users where mailing_list_mode = 't'```
1. notification_level = 3 ~~ means they are tracking the category
1. category_id = 15 ~~ is the category number that we're using the badge to represent
1. mailing_list_mode = 't' ~~ says if they are in mailing list mode (getting notified for ALL messages into the system) then we will also count them as a subscriber for the category too.
Now a moderator can look at ```/badges``` and determine who is Watching their category.
There are a few tweaks that we should do to improve the badge on the SQL, those include:
1. Someone could be in mailing_list_mode, but they might have MUTED a category, we should take that into consideration
1. notification_level could possibly be switched to >= 2 so it would also capture if they were TRACKING or WATCHING a category.
The release of the data explorer could also be a big help, we just haven't had a chance to play with that much yet.