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.

No, when you make a http request, the route is a part of the headers. DNS is only the name part.

That's being done with a reverse proxy.

Easiest way to accomplish what you want: host a static html page on blog.example.com that redirects the user to example.com/blog:

https://stackoverflow.com/questions/5411538/how-to-redirect-one-html-page-to-another-on-load

I’ll note that a server side redirect would be preferred if this is for professional or business use. For personal shit, go nuts

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.