Regarding YouTube in particular, I tend to open up videos in background tabs for later viewing and find it very annoying that they start playing automatically before I get around to that tab. I did go there to watch the video—eventually. Just not the second that the page finishes loading.
YMMV. A persistent setting to enable or disable auto-play would be ideal.
Actually it doesn't even seem to load most of the page in a background tab until it's focused... which is also annoying. (Or perhaps it's just the parts hidden behind 'onload' JavaScript, which these days is most of the page content.) Part of the reason for opening the tab in the background is getting the lengthy loading process out of the way while I'm reading something else.
For this reason I tend to activate the tab and then go back to what I was doing before... and then the video auto-plays.
The first thing I do when hitting a YouTube URL is stop the video. Then I'll either run youtube-dl on the URL, or just paste it straight into a proper video player (VLC).
YMMV. A persistent setting to enable or disable auto-play would be ideal.