I struggled with this, too. Here's what I found. Expanding on Adam's answer, here's a bit more detail, including how to encode VP9 with alpha in a WebM container.
First, here's a CodePen playground you can play with. Feel free to use my videos for testing.
<video width="600" height="100%" autoplay loop muted playsinline>
<source src="https://rotato.netlify.app/alpha-demo/movie-hevc.mov" type='video/mp4'; codecs="hvc1">
<source src="https://rotato.netlify.app/alpha-demo/movie-webm.webm" type="video/webm">
</video>
And here's a full demo page using z-index to layer the transparent video on top and below certain elements. (You can clone the Webflow template.)
So, we'll need a WebM movie for Chrome, and an HEVC with Alpha (supported by Safari on all platforms since 2019).
Which browsers are supported?
For Chrome, I've tested successfully on version 30 from 2013. (Caniuse WebM doesn't seem to say which WebM codec is supported, so I had to try my way.) Earlier versions of Chrome seem to render a black area.
For Safari, it's simpler: Catalina (2019) or iOS 11 (2019).
Encoding
Depending on which editing app you're using, I recommend exporting the HEVC with Alpha directly.
But many apps don't support the WebM format, especially on Mac, since it's not a part of AVFoundation.
I recommend exporting an intermediate format like ProRes4444 with an alpha channel to not lose too much quality at this step. Once you have that file, making your WebM is as simple as:
ffmpeg -i "your-movie-in-prores.mov" -c:v libvpx-vp9 movie-webm.webm
See more approaches in this blog post.