```
DROP TABLE IF EXISTS test_push_down;

CREATE TABLE test_push_down
ENGINE = AggregatingMergeTree
ORDER BY n AS
SELECT 
    intDiv(number, 25) AS n, 
    avgState(number) AS s
FROM numbers(2500)
GROUP BY n;

SET enable_debug_queries = 1;

ANALYZE SELECT *
FROM 
(
    SELECT 
        n, 
        finalizeAggregation(s)
    FROM test_push_down
)
WHERE (n >= 2) AND (n <= 5)
```

Result is 
```
SELECT 
    n, 
    `finalizeAggregation(s)`
FROM 
(
    SELECT 
        n, 
        finalizeAggregation(s)
    FROM test_push_down
)
WHERE (n >= 2) AND (n <= 5)
```

W/o `finalizeAggregation` - it was pushed down:
```
ANALYZE SELECT *
FROM 
(
    SELECT 
        n, 
        s
    FROM test_push_down
)
WHERE (n >= 2) AND (n <= 5)
```

```
SELECT 
    n, 
    s
FROM 
(
    SELECT 
        n, 
        s
    FROM test_push_down
    WHERE (n <= 5) AND (n >= 2)
)
WHERE (n >= 2) AND (n <= 5)
```

It looks like the reason is finalizeAggregation is marked as 'stateful' and for stateful functions

https://github.com/ClickHouse/ClickHouse/blob/89e967333662c8ec4c46dfa1ba12bd10781538b9/src/Interpreters/PredicateExpressionsOptimizer.cpp#L93-L94

https://github.com/ClickHouse/ClickHouse/blob/e4689ce302524e73477bff25370a2da67034b4ef/src/Functions/finalizeAggregation.cpp#L36-L39

Introduced in https://github.com/ClickHouse/ClickHouse/pull/3890

@zhang2014 is there some good reason why `finalizeAggregation` is marked as statefull?
{
        const DataTypeAggregateFunction * type = checkAndGetDataType<DataTypeAggregateFunction>(arguments[0].get());
        if (!type)
        {
            throw Exception(ErrorCodes::ILLEGAL_TYPE_OF_ARGUMENT,
                "Argument for function '{}' must have type AggregateFunction - state of aggregate function."
                " Got '{}' instead", getName(), arguments[0]->getName());
        }
        return type->getReturnType();
    }
    void executeImpl(Block & block, const ColumnNumbers & arguments, size_t result, size_t ) const override
    {
        auto column = block.getByPosition(arguments.at(0)).column;
        if (!typeid_cast<const ColumnAggregateFunction *>(column.get()))
            throw Exception("Illegal column " + block.getByPosition(arguments.at(0)).column->getName()
                    + " of first argument of function "
                    + getName(),
                ErrorCodes::ILLEGAL_COLUMN);
        
        auto mut_column = IColumn::mutate(std::move(column));
        block.getByPosition(result).column = ColumnAggregateFunction::convertToValues(std::move(mut_column));
    }
};
}
void registerFunctionFinalizeAggregation(FunctionFactory & factory)
{
    factory.registerFunction<FunctionFinalizeAggregation>();
}
}