So I've long felt that this card is generally undervalued but hadn't actually played it until recently with the taboo. Now I firmly believe that this is one of the most maligned and misrepresented cards in the community. The perception that it is a bad card seems to come from people who want the card to be a class-defining archetype card that allows rogues to "fight with their ". That's why you see reviews suggesting it is inferior to the Ornate Bow, mentioning that you only get "one shot per round", or that the effect would work better as a permanent. That's not what this card does; it's a boosting asset similar to High Roller or Well Prepared for rogue firearm decks that already can fight COMPETANTLY with their fist but need a boost to reliably hit the "succeed by X" effects on their firearm tests. If your deck struggles to land attacks without Sharpshooter on the table than sharpshooter won't fix your deck. But if you're a rogue (other than Tony Morgan) that can expect to take tests with cards like the Beretta M1918, .41 Derringer or even the Sawed-Off Shotgun every round then this card is definitely worth considering.
The best user by far is still Winifred Habbamock, who is able to get by fine with skill cards and her 3 while waiting to draw sharpshooter. She gets a plus two right from her base stats before she can potentially get even higher by shooting at the lowest stat on the enemy's card. There's is pretty much no reason not to push your skill value as high as possible with Winifred when you might be going All In with Lucky Cigarette Case, and Sharpshooter can help you get there if you are playing a fighty Winifred deck.
"Skids" O'Toole gets a plus one to his tests from his base stats and loves to use firearms with his access to guardian firearm support cards like the Cleaning Kit. He also gets access to the incredible Wolf Mask to help him pass tests both before he draws his sharpshooter and can be combined with it to make sure he's succeeding by what's needed.
Finn Edwards has a very similar stat line to Skids and has access to very relevant boosting cards like Track Shoes and Peter Sylvestre, however sharpshooter has major competition in a Finn deck with the other boosting asset, Dirty Fighting. A deck that includes both is conceivable as Finn is probably at around a 7 skill value with both a firearm and dirty fighting, so he might need a larger boost depending on which firearms he is using
Kymani Jones may be interested in a one-of sharpshooter as an investigator that sometimes runs firearms to help them soften up enemies before finishing them off. However it's worth noting that the most frequent firearm for this is the .25 Automatic which does not have succeed by bonuses. With sharpshooter in the deck it opens up additional options such as just going for the kill with an enemy that's engaged with another investigator using something like the Mauser C96
Tl:dr This card is fine when it is used the way it supposed to be and the recent taboo puts right in line with similar boosting assets like High Roller (although I do wish it was "trick" card)