A very simplified comparison would be something like:
Sniper
-
-Better stat growths
-Don't have to train Lance and Riding
Bow Knight
-Mobility, bow range, Canto utility
-
-Need to train Lance and Riding
For the most part, Sniper does win out mainly because Hunter's Volley is what allows archers to ORKO in the midgame while only using cheap bows like Steel+.
And Bow Knight is often considered a terrible class mainly because of the bad stat modifiers.
However, a case can be made that promoting into Bow Knight in the lategame is much better for some characters:
-In lategame Maddening, many characters, especially the default archers of each House (Ashe, Bernadetta, Ignatz) will have trouble ORKOing even with Hunter's Volley
-By the time you have access to Bow Knight, you also have access to Brave Bows which partially make up for the lack of Hunter's Volley
-Stat growths are not as important lategame
-Lategame Maddening throws a lot of tricky situations at you, where mobility, range, and Canto utility are more useful than the raw damage of Hunter's Volley
I find this to be especially true for Bernadetta. She will certainly lack the damage to ORKO reliably even with Hunter's Volley, unless she got extremely stat blessed and/or you funneled a lot of resources into making her as strong as possible.
But most importantly she becomes an amazing utility character thanks to
Encloser(IMO tied with Swift Strikes as the best non-Relic combat art in the game). More specifically, Encloser combined with the aforementioned trifecta of mobility, range, and Canto instantly makes Bernadetta one of the most useful units for Maddening, regardless of her stats. This gives you so many more options in dealing with the tricky situations, especially Demonic Beasts, and lets you easily maintain control of the flow of battle. At this point in the game, it is always better for her to lock down a threatening enemy unit than for her to attempt to delete that threat with Hunter's Volley (and doing like 20 damage).
Also she has Lance boon and Riding hidden talent.
Also, as a Sniper, she wastes one of her special tricks in Vengeance. As a Bow Knight it is surprisingly useful even if you don't build her around it.
Ashe and Ignatz also bring some utility with their debuff shots, but not to the same extent as Bernadetta's Encloser. Honestly though, I usually bench Ashe and Ignatz around the point they start being unable to ORKO with Hunter's Volley, if not earlier. At the very least, promoting into Bow Knight lets these characters transition effectively into utility/chip damage/aggro supports, rather than pure offensive units that still only do chip damage anyway.
As a side note, Ashe and Ignatz can get Poison Strike by dipping into Dark Mage, which should significantly increase their chip damage usefulness, but the resources you would devote to that are better used on pretty much any other character. Might be fun to play around with if you're swimming in NG+ renown.
Which reminds me, archer Hubert is a thing, as well as archer Mercedes and Hanneman, but I do not have any experience with "magic archer" builds in 3H to discuss if they would be better as Snipers or Bow Knights.
Shamir and things like archer Felix or Byleth are better off staying as Snipers because they can still ORKO in the lategame with Hunter's Volley, and they don't have any notable utility that would be amplified by the mobility/range/Canto of a Bow Knight.
Leonie does make a good Bow Knight since Canto + Point Blank Volley is about as good as Hunter's Volley anyway (it's a trade between Canto utility and having to take a counter attack if PBV doesn't kill). That said, she can do the same thing as any mounted class, so it's ultimately a choice between the range of Bow Knight vs better stats of other mounted classes.
Same goes for Cyril
Claude excels as either Sniper or Bow Knight, since his stats and skillset allow him to fully utilize the advantages of either class. However, his unique class is probably the best choice anyway.
In summary,
-Bernadetta should be promoted to Bow Knight when the time comes
-Same for Ashe and Ignatz, if you really want to keep them in you roster past midgame
-Shamir should probably stay Sniper, as with other "pure power" kind of archers who can ORKO just fine with Hunter's Volley and don't have any special utility as Bow Knight.
-Leonie and Cyril shouldn't even be Snipers in the first place, except for growths maybe, since they inherently have access to what is the main reason for being a Sniper. Any class that gives them Canto should allow them to reach their full potential.
In the lower difficulties, Sniper is usually better, except maybe Bernadetta, because most units can easily ORKO, or at least deal heavy damage, with Hunter's Volley, and you don't need chip damage utility when you can just delete enemies anyway.