Как рассчитать долю доставок с первой попытки?

Условие

В PostgreSQL есть campaigns(id) и attempts(id PRIMARY KEY, campaign_id, user_id, status, event_at), все поля NOT NULL. delivered означает успех. Знаменатель метрики — пользователи хотя бы с одной попыткой в кампании; числитель — те, чья первая попытка успешна. При равных event_at порядок задаёт id. Для кампаний без попыток верните NULL.

Ответ

Выделите первую попытку каждой пары campaign_id/user_id, усредните её флаг успеха и присоедините агрегат к полному списку кампаний через LEFT JOIN.

WITH ranked AS (
  SELECT campaign_id, status, ROW_NUMBER() OVER (
    PARTITION BY campaign_id, user_id ORDER BY event_at, id
  ) AS rn FROM attempts
), metric AS (
  SELECT campaign_id,
    AVG(CASE WHEN status = 'delivered' THEN 1.0 ELSE 0.0 END) AS rate
  FROM ranked WHERE rn = 1 GROUP BY campaign_id
)
SELECT c.id, m.rate
FROM campaigns c LEFT JOIN metric m ON m.campaign_id = c.id;

Если у пользователя первая попытка неуспешна, а вторая успешна, в числитель он не входит. NULL для пустой кампании означает отсутствие наблюдений, а не нулевую эффективность.

Источник: www.postgresql.org.