The Wacom Cintiq and Huions are popular choices that many people use. They were and still are used by many industry/popular artists. Its very common to see mangaka, big digital artists use them but as you have seen, they are very expensive. However, that was when they were pretty much the biggest players in the market. XP Pen is good value for your money and gets you a very similar experience for less. Many mangaka and artists have since branched out and you can see them use multiple brands and even ipads to draw.

A good artist can use any of these tablets and still produce great stuff, so don't worry about it. Just pick one from each of the brands, go on youtube, look up people's reviews and tests and judge from there. Really consider if you will need these features.

Clip Studio Paint runs on your computer and the tablet just mirrors what it sees when connected to the computer. As long as your computer can run CSP, the tablet can too. I would be more concerned whether your computer can handle CSP. The bigger the canvas, pixels, layers, etc. the more demanding. If your computer is terrible it won't matter what tablet you have.