There is limited modern evidence (ie. in a world where everyone is brushing their teeth with fluoride toothpaste) of some reduction in tooth decay in children. There were no studies on adults that met the review criteria.
Overall it seems like we just don't really know how much impact CWF currently has.
There are two Cochrane reviews that I saw on community water fluoridation:
https://pubmed.ncbi.nlm.nih.gov/26092033/
https://pubmed.ncbi.nlm.nih.gov/39362658/
There is limited modern evidence (ie. in a world where everyone is brushing their teeth with fluoride toothpaste) of some reduction in tooth decay in children. There were no studies on adults that met the review criteria.
Overall it seems like we just don't really know how much impact CWF currently has.