Как посчитать доставку промокампаний без двойного счёта?

Условие

В PostgreSQL campaigns(id) содержит все кампании. communications(id, campaign_id, user_id, status) содержит попытки доставки; status равен delivered или failed. Все поля обязательны. Для каждой кампании посчитайте пользователей хотя бы с одной успешной попыткой и пользователей с попытками, но без успеха. Кампании без попыток тоже нужны.

Ответ

Сначала сведите попытки к одной строке на кампанию и пользователя с флагом успеха. Затем присоедините результат к campaigns и посчитайте успешные и неуспешные группы.

WITH per_user AS (
  SELECT campaign_id, user_id,
    MAX(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS ok
  FROM communications GROUP BY campaign_id, user_id
)
SELECT c.id,
  SUM(CASE WHEN p.ok = 1 THEN 1 ELSE 0 END) AS delivered_users,
  SUM(CASE WHEN p.ok = 0 THEN 1 ELSE 0 END) AS failed_users
FROM campaigns c LEFT JOIN per_user p ON p.campaign_id = c.id
GROUP BY c.id;

Пользователь с ошибкой и последующим успехом учитывается только как получивший сообщение. Пользователей без единой попытки здесь нельзя отличить от не входивших в аудиторию: для этого потребовалась бы отдельная таблица назначения.