If you think about this one way, any Pokemon can be useful in competitive battles if your opponent is dumb enough to spam heal pulse or healing wish. Dumbness is a relative thing, so more people will be dumb if you become better at competitive battling. I've seen people win major tournament battles with only 5 Pokemon. You can probably accomplish similar feats with Caterpie if you get good enough.
If you think about it another way, there is no reason to choose Caterpie over other Pokemon if you want to win as often as possible. The 6 Pokemon Butterfree, Beautifly, Dustox, Mothim, Leavanny, and Vivillon can learn all 5 of Caterpie's moves (including snore and electroweb) and are better than Caterpie in all 6 stats. I don't think any popular formats ban any of those 6 Pokemon, so Caterpie is completely outclassed by something no matter the situation.
Of course, you can create your own format where the only Pokemon allowed is Caterpie. Caterpie would have to be useful in that format.