I think diminishing returns make 240Hz not worth it for the average competitive player. There are 2 exceptions:

1. You're very competitive

2. You've got the money to spend

Here's one way to look at it 60Hz is 1 refresh every 17 milliseconds. At 165Hz it's every 6ms and at 240Hz it's every 4ms. So from that perspective whatever premium you're paying is to have the opponent's head appear on the screen 2ms faster (also impacted by other hardware induced delays). For context, the average person's response time to visual stimulus is about 250ms.

It's definitely nice to have and I haven't gone above 165Hz myself as a disclaimer but you will need to consistently hit 240 FPS in games to make use of the extra refresh rate and that requires a beefy CPU and/or GPU depending on the game. Especially if you want your 1% lows (FPS dips) to stay above 240 as well.

To my eyes, the main benefit of a higher refresh rate for fast paced games is the smoothness of motion (at least CSGO which is what I play). If I can track an enemy's head then I'm good and I don't think you need 240Hz for that.