А можно ли что-то ещё придумать вместо обычного CTE, учитывая, что фильтрация всё равно происходит в памяти на всей таблице?

CTE — прежде всего способ организации запроса, а не гарантия материализации или фильтрации в памяти. Альтернатива зависит от плана: ранний фильтр, индекс, временная таблица или предрасчёт.

Сначала проверьте, где фильтр реально применяется и почему источник читает много строк. В некоторых СУБД CTE встраивается в общий план; в других случаях материализация может мешать переносу условий.

Временная таблица с подходящим индексом полезна при многократном использовании дорогого промежуточного результата. Материализованная витрина уместна при повторяемом расчёте и допустимой задержке данных. Без самого запроса нельзя обоснованно выбрать единственный вариант или обещать ускорение.