Use an A (and AAAA) when you know the stable IP, or when you are at the zone apex. Use a CNAME when a vendor says “point this hostname at us” and they reserve the right to change IPs. The resolver follows the CNAME, then looks up A/AAAA on the target. Each extra hop costs time, so do not CNAME a name to a name to a name to a CDN unless you must.
Why www is easy and the apex is not
www.example.com can be a CNAME to example.com or to sites.vendor.net. example.com already has SOA and NS records, so a CNAME there would break the rule that a CNAME is the only data at that node. Workarounds: ALIAS/ANAME/flattened CNAME features at some hosts, which synthesize A/AAAA at query time. Those are not visible as a CNAME in a standard lookup — you will see A records instead. That is expected.
Mail and CNAME
Do not CNAME a name that also needs MX or TXT if the RFC conflict would drop those records. Put MX on the apex; put web aliases on subdomains.