Я расширил этот запрос, чтобы немного помочь с моим пунктом №2 — я могу сделать вывод и определить, кто, вероятно, был последними отписавшимися:
-- список пользователей с отрицательными настройками электронной почты с указанием их УД (уровень доверия), дат взаимодействий и баллов «Спасибо»
SELECT u.ID "ID пользователя"
,u.USERNAME "Имя пользователя"
,u.trust_level "УД"
-- следующая строка опционально включает общий балл «Спасибо» из плагина Gamification. См. второе соединение ниже.
,di.gamification_score "Спасибо"
,CAST (u.first_seen_at AS DATE) "Присоединился"
,CAST (u.last_seen_at AS DATE) "Был в сети"
,CAST (u.last_emailed_at AS DATE) "Получил письмо"
,CASE uo.email_digests
WHEN 't' THEN 'Да'
WHEN 'f' THEN 'Нет'
ELSE 'Не установлено'
END "Рассылка?"
,CASE uo.email_level
WHEN 0 THEN 'НИКОГДА'
WHEN 1 THEN 'Вне сети'
WHEN 2 THEN 'Всегда'
END "Уровень писем"
,CASE uo.email_messages_level
WHEN 0 THEN 'НИКОГДА'
WHEN 1 THEN 'Вне сети'
WHEN 2 THEN 'Всегда'
END "Уровень сообщений"
FROM USERS u
LEFT OUTER JOIN USER_OPTIONS uo
ON u.ID = uo.USER_ID
-- это второе соединение включает общий балл «Спасибо» из плагина Gamification
LEFT OUTER JOIN directory_items di
ON u.ID = di.USER_ID AND di.period_type = 1
-- используйте следующий оператор SELECT для списка пользователей, не получающих письма о активности тем
WHERE uo.email_digests = 'f' OR (uo.email_level = 0)
-- или используйте следующий оператор SELECT для списка пользователей, не получающих ни писем о активности тем, ни писем о активности сообщений
-- WHERE uo.email_digests = 'f' OR (uo.email_level = 0 AND uo.email_messages_level = 0)
AND u.ID <> -1
ORDER BY u.last_emailed_at DESC
…однако без некоторых дат и деталей запросов на отписку картина не может быть полной.
По сути, меня интересует следующее: какое письмо вызвало отписку; отписались ли они от рассылки или от всего; был ли отписавшийся активным давним пользователем, который, возможно, потерял интерес, или это был неактивный пользователь с УД0, который изначально не был особенно заинтересован?
