with
no_vowels_ as (
select word,
replace(word, 'a', '') as no_as,
replace(no_as, 'e', '') as no_es,
replace(no_es, 'i', '') as no_is,
replace(no_is, 'o', '') as no_os,
replace(no_os, 'u', '') as no_us,
replace(no_us, 'y', '') as no_vowels,
from wordnik
),
no_vowels as (select word, no_vowels from no_vowels_)
-- select * from no_vowels
-- where no_vowels = 'ttt'
-- order by length(word) desc
-- select no_vowels,
-- length(no_vowels) as n_consonants_in_pattern,
-- count(*) as n_words,
-- string_agg(word, ', ')
-- from no_vowels
-- where right(no_vowels,1) != 's' and right(no_vowels, 2) != 'ng'
-- group by all
-- having n_consonants_in_pattern >= 4
-- order by n_words desc
select no_vowels,
length(no_vowels) as n_consonants_in_pattern,
count(*) as n_words,
string_agg(word, ', ')
from no_vowels
where length(replace(no_vowels, substr(no_vowels, 1, 1), '')) = 0
and n_consonants_in_pattern >= 2
group by all
order by n_consonants_in_pattern desc
