Да там для других целей нужна память, я могу увеличить

чтобы хватило, но суть вопроса не в этом.
max_memory_usage выставлен в 300Г
max_bytes_before_external_group_by в 100

интересно, если разбить результат на бакеты из 100 строк, и потом проаггрегировать, поможет?

select
-- финальная группировка
SUM(TotalDistinctKeysCount) AS TotalDistinctKeysCount,
SUM(keys_with_aaa_event) AS keys_with_aaa_event,
SUM(keys_with_bbb_event) AS keys_with_bbb_event,
SUM(keys_with_ccc_event) AS keys_with_ccc_event,
SUM(keys_with_ddd_event) AS keys_with_ddd_event,
SUM(keys_with_2_aaa_events) AS keys_with_2_aaa_events,
SUM(keys_with_2_bbb_events) AS keys_with_2_bbb_events,
SUM(keys_with_2_ccc_events) AS keys_with_2_ccc_events,
SUM(keys_with_2_ddd_events) AS keys_with_2_ddd_events,
SUM(keys_with_many_aaa_events) AS keys_with_many_aaa_events,
SUM(keys_with_many_bbb_events) AS keys_with_many_bbb_events,
SUM(keys_with_many_ccc_events) AS keys_with_many_ccc_events,
SUM(keys_with_many_ddd_events) AS keys_with_many_ddd_events
from
(
-- подзапрос группирует по бинам
SELECT
key1%16 as subpart,
count(*) AS TotalDistinctKeysCount,
countIf(aaa_count < 20) AS keys_with_aaa_event,
countIf(bbb_count < 20) AS keys_with_bbb_event,
countIf(ccc_count < 20) AS keys_with_ccc_event,
countIf(ddd_count < 20) AS keys_with_ddd_event,
countIf(aaa_count >=20 and aaa_count<70) AS keys_with_2_aaa_events,
countIf(bbb_count >=20 and bbb_count<70) AS keys_with_2_bbb_events,
countIf(ccc_count >=20 and ccc_count<70) AS keys_with_2_ccc_events,
countIf(ddd_count >=20 and ddd_count<70) AS keys_with_2_ddd_events,
countIf(aaa_count >= 70) AS keys_with_many_aaa_events,
countIf(bbb_count >= 70) AS keys_with_many_bbb_events,
countIf(ccc_count >= 70) AS keys_with_many_ccc_events,
countIf(ddd_count >= 70) AS keys_with_many_ddd_events
from (
-- подзапрос возвращает миллиарды строк
SELECT
key1 as key1,
key2 as key2,
countIf(eventid = 35) AS aaa_count,
countIf(eventid = 31) AS bbb_count,
countIf(eventid = 30) AS ccc_count,
countIf(eventid IN (30, 32, 31, 28, 35)) AS ddd_count
from (
select rand()%10 key1, rand(2)%12 key2, rand(3)%40 eventid from numbers(100000)
)
GROUP BY
key1,
key2
)
GROUP BY subpart
);

40 млрд строк огромная компания чё бы не лицензию не купить на вертику и так не мучиться?