My experience is that tarballs and RPMs are most common. Regardless there's a lot more diversity in how things are packaged on Linux than on Windows or OSX.
Even if I get a tarball on OSX I can double click on it and then drag the application into the "Applications" directory or run the installer. I'm not sure why this is so challenging on Linux.
Both macOS and windows made the choice to make programs mostly self contained things that can be just dragged in.
Linux distros have a complex network of dependancies, config files, and general crap that requires sophisticated tooling to manage which is different on different distros. All to save a few kb of storage space.
When you download a tarball from the internet containing an application, usually (unless they messed up/don't know what they're doing) that means most if not all dependencies are fully contained in that archive, except for things you can't really do that for, like libc. The point of a tarball is to avoid the system package manager, which makes it easier to reach more distros at the cost of an increased risk it simply won't work on some systems (due to missing system libraries or different versions).
The real reason macOS and Windows are easier to distribute software on is because there's only one Windows and only one macOS. "Linux" is not an operating system, Ubuntu, Arch, Fedora, RHEL, etc. are all different operating systems that just so happen to share a kernel and a similar ecosystem of open source software. So compatibility between them is more of a coincidence than anything else.
Luckily nowadays we have Flatpaks and AppImages, which both solve the problem of simplifying app distribution and installation universally for all distros. Although devs will probably get a lot of hate if they only distribute their app as a Flatpak or AppImage and don't also offer tarballs/debs/rpms...so app distribution is likely to remain complicated for a while.
> Even if I get a tarball on OSX I can double click on it and then drag the application into the "Applications" directory or run the installer.
All without opening or requiring the user to open a hideous terminal just to run the app.
> I'm not sure why this is so challenging on Linux.
Desktop Linux fundamentally can never solve this problem. All of the packaging alternatives of alternatives have created a mess and confusion around which distro can open what and not only these alternatives even work out of the box, there is no guarantee that all Linux distros will be supported for that.
At most it is a 'solution' creating a new problem; whist still looking for a problem to solve.
