Query for users' email settings

Data & reporting
1

How could I find out how many users have each email setting?

I’m asking as only about a tenth of users watching a topic are being emailed post notifications.

Thank you.

2

The ones in their Preferences/Emails page? That field is in the user_options table (email_level), and there’s also the private message one too (email_messages_level).

I think this one works. It pulls out the users watching a particular topic, and counts them by email level:

Key Email Level
0 Always
1 Only when away
2 Never 
-- [params]
-- int :topic_id

SELECT tu.topic_id AS topic_id, 
uo.email_level, 
COUNT(*)
FROM user_options uo
JOIN topic_users tu ON tu.user_id = uo.user_id
WHERE tu.notification_level = 3
AND tu.topic_id = :topic_id 
GROUP BY topic_id, uo.email_level

Let me know if I’ve mucked it up. :slightly_smiling_face::+1:

Update:

I think this one works too: (and displays a bit neater)

-- [params]
-- int :topic_id

SELECT tu.topic_id AS topic_id, 
COUNT(CASE WHEN tu.notification_level = 3 THEN 1 END) AS watching, 
COUNT(CASE WHEN uo.email_level = 0 THEN 1 END) AS always, 
COUNT(CASE WHEN uo.email_level = 1 THEN 1 END) AS only_when_away, 
COUNT(CASE WHEN uo.email_level = 2 THEN 1 END) AS never
FROM user_options uo
INNER JOIN topic_users tu ON tu.user_id = uo.user_id
WHERE tu.notification_level = 3
AND tu.topic_id = :topic_id 
GROUP BY topic_id
1 Like