CNAMEs can only point to other domains. Redirects like that would be handled on the HTTP level, so you'd need a web server in the mix that sends requesters to the right place when they try to access the subdomain. It can redirect to anywhere, not just domains you control, so the Bluesky example would be handled the same way as the other one.
Not strictly within the normal way DNS works, no. A CNAME record response can only contain another domain or subdomain name. You would have to run a webserver that listens on the IP that the CNAME record eventually pointed to, in order to handle redirections to a specific URL.