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.
¿Los de su página de Preferencias/Correos electrónicos? Ese campo está en la tabla user_options (email_level), y también está el de mensajes privados (email_messages_level).
Creo que este funciona. Extrae los usuarios que siguen un tema en particular y los cuenta por nivel de correo electrónico:
| Clave | Nivel de correo electrónico |
|---|---|
| 0 | Siempre |
| 1 | Solo cuando está ausente |
| 2 | Nunca |
-- [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
Avísame si lo he estropeado. 
Actualización:
Creo que este también funciona: (y se muestra un poco más ordenado)
-- [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 me gusta