Untitled

WITH public AS (
  SELECT 
    u.id as recipient_id, 
    GETDATE() as sent_at, 
    192598 as group_id, 
    -1 as email_id 
  from 
    can2_production.users u 
  where 
    u.id IN (
      30946954, 209658183, 42511686, 99596773, 
      21530261, 39481829, 21363069, 269088354, 
      254352444, 237725984
    )
), 
engagement AS(
  SELECT 
    recipient_id, 
    group_id, 
    action_type, 
    created_at 
  FROM 
    (
      SELECT 
        recipient_id, 
        email_id, 
        action_type, 
        group_id, 
        created_at, 
        ROW_NUMBER() OVER(
          PARTITION BY recipient_id, 
          email_id, 
          action_type 
          ORDER BY 
            created_at
        ) AS rn 
      FROM 
        can2_production.email_activities_16 
      WHERE 
        created_at BETWEEN '2024-06-18 23:50:56.206885' 
        AND '2024-07-19 01:50:56.206885' 
        AND recipient_id in (
          Select 
            recipient_id 
          from 
            public
        ) 
        AND action_type IN (
          'sent', 'open', 'verified_open', 'click'
        )
    ) AS sub 
  WHERE 
    sub.rn = 1
) 
SELECT 
  recipient_id, 
  sent_count AS sent_count_30_days, 
  open_count AS open_count_30_days, 
  click_count AS click_count_30_days, 
  CASE WHEN sent_count = 0 
  AND open_count = 0 THEN 0 WHEN sent_count = 0 
  AND open_count > 0 THEN 1 ELSE LEAST(open_count :: FLOAT / sent_count, 1) END AS open_rate_30_days, 
  CASE WHEN sent_count = 0 
  AND click_count = 0 THEN 0 WHEN sent_count = 0 
  AND click_count > 0 THEN 1 ELSE LEAST(
    click_count :: FLOAT / sent_count, 1
  ) END AS click_rate_30_days, 
  sent_count_in_group AS sent_count_in_group_30_days, 
  open_count_in_group AS open_count_in_group_30_days, 
  click_count_in_group AS click_count_in_group_30_days, 
  CASE WHEN sent_count_in_group = 0 
  AND open_count_in_group = 0 THEN 0 WHEN sent_count_in_group = 0 
  AND open_count_in_group > 0 THEN 1 ELSE LEAST(
    open_count_in_group :: FLOAT / sent_count_in_group, 
    1
  ) END AS open_rate_in_group_30_days, 
  CASE WHEN sent_count_in_group = 0 
  AND click_count_in_group = 0 THEN 0 WHEN sent_count_in_group = 0 
  AND click_count_in_group > 0 THEN 1 ELSE LEAST(
    click_count_in_group :: FLOAT / sent_count_in_group, 
    1
  ) END AS click_rate_in_group_30_days 
FROM 
  (
    SELECT 
      public.recipient_id, 
      COUNT(
        CASE WHEN engagement.action_type = 'sent' THEN 1 END
      ) AS sent_count, 
      COUNT(
        CASE WHEN engagement.action_type IN ('open', 'verified_open') THEN 1 END
      ) AS open_count, 
      COUNT(
        CASE WHEN engagement.action_type = 'click' THEN 1 END
      ) AS click_count, 
      COUNT(
        CASE WHEN engagement.action_type = 'sent' 
        AND engagement.group_id = 192598 THEN 1 END
      ) AS sent_count_in_group, 
      COUNT(
        CASE WHEN engagement.action_type IN ('open', 'verified_open') 
        AND engagement.group_id = 192598 THEN 1 END
      ) AS open_count_in_group, 
      COUNT(
        CASE WHEN engagement.action_type = 'click' 
        AND engagement.group_id = 192598 THEN 1 END
      ) AS click_count_in_group 
    FROM 
      public 
      LEFT JOIN engagement ON public.recipient_id = engagement.recipient_id 
      AND engagement.created_at < public.sent_at 
      AND public.sent_at - engagement.created_at <= INTERVAL '30 days' 
    GROUP BY 
      public.recipient_id
  )