if you go to a water park say somewhere like Spain and go on those fucking terrifying crazy "highest water slide in the world" type things and had an accident would that count as a high risk activity
You have to read the T&Cs for each policy as it varies as to what is considered a 'high risk activity'. If a particular insurance company or the industry in general has had a lot of pay outs due to accidents with any particular activity/water park, they could indeed exclude certain types of slides or a particular one. You'd probably be wise not to assume that all insurance policies cover all public waterparks - you'd hope they do, but they might not.
We scuba dive and mountain bike, so are used to reading the small print, as different policies treat different types of mountain biking differently - is it cross country or downhill - obviously in a lot of cases something like this is subject to opinion - there is an element of 'going downhill' on most mountain bike rides - but when does it stop being 'cross country' and start being 'downhill' which is more dangerous.
Likewise with diving. There are depth limits, and the deeper, the more risky, in general. Sometimes, it's to a certain depth, sometimes, it's to the individual diver's qualification limit, sometimes, it's only if you have a guide and are diving with a commercial operator etc etc.
It's interesting what different companies consider to be risky activities. The classic one often overlooked is moped hire - lots of people do this and won't be covered by their travel insurance, but maybe you can buy additional insurance when you hire the moped, but again you'd have to check that it covered you against accidents, hospital stays, specialist transport home whether dead or alive, the costs of someone staying with you during an extended hospital stay etc etc, because that's the reality of the potential risk when hiring a moped abroad, other side of road, often less safe than the UK.