When you write an aggregate query, you can use the HAVING clause to filter the results, e.g.:
select shape,
colour,
count(*) cnt
from shapes
where colour != 'black'
group by shape,
colour
having count(*) > 10;
However, when you have an analytic function that you want to filter results on, you have to use a subquery, e.g.:
select shape,
colour,
cnt
from (select shape,
colour,
count(*) over (partition by shape, colour) cnt
from shapes
where colour != 'black')
where cnt > 10;
It would aid readability if we could have the same sort of functionality as aggregate queries, something along the lines of:
select shape,
colour,
count(*) over (partition by shape, colour) cnt
from shapes
where colour != 'black'
filtering on count(*) over (partition by shape, colour) > 10;