SELECT
   Date,
   SUM(Sent) AS AllSent,
   SUM(Opened) AS AllOpened,
   SUM(Clicked) AS AllClicked,
   SUM(Sent) > 0 AS UniqueSent,
   SUM(Opened) > 0 AS UniqueOpened,
   SUM(Clicked) > 0 AS UniqueClicked
FROM Events_Buffer AS t1 ANY INNER JOIN (
       SELECT
           MessageId,
           Identity,
           SUM(multiIf(Type = 'Sent', 1, 0)) AS Sent,
           SUM(multiIf(Type = 'Opened', 1, 0)) AS Opened,
           SUM(multiIf(Type = 'Clicked', 1, 0)) AS Clicked
           FROM Retention.Events_Local WHERE (Type IN ('Sent', 'Opened', 'Clicked')) AND (Realm = 'dating') AND (Date >= '2020-07-20') AND (Date <= '2020-07-27') AND (Channel IN ('electronicmail')) GROUP BY MessageId, Identity) AS t2
ON t2.MessageId = t1.MessageId AND t2.Identity = t1.Identity
WHERE (Type = 'Sent') AND (Date >= '2020-07-20') AND (Date <= '2020-07-27') GROUP BY Date, Identity, MessageId

В правой таблице 894507 на одной из шард

Дело точно не в памяти, т.к. этот же запрос сейчас работает, если не указывать джойн на buffer