Water levels can be a great change of pace when done right, even in games that have a number of lacklustre water stages they can still hide a great one within, that's pretty much how I feel about Super Mario World which has rather ho-hum water stages for the most part yet also packs Vanilla Fortress, a fully submerged stage with the hazards home to castle and fort stages, then there's Mondo, an overworld stage with a strong current of rising and lowering water running through it.
Rayman Origins showed promise with water stages, they controlled fluidly with an easily grasped control scheme but I found the stages themselves to lack that extra bit of inspiration in design (I guess it didn't help that the later world just repackaged the existing ideas). Its follow up Legends blended Origins style of water stages with light stealth like elements and in turn led to stronger water stages and perhaps some of the better ones I've played in any game. The advantage here was that most hazards used an area of effect to try and catch the player but didn't immediately harm you if you got out of view as quick as possible leading to a bit more leeway when making a small error in movement as well as just being a kind of fun gimmick in general.
Sonic Colours Aquarium Park stages took on a different approach to other watery stages seen in the series by at last giving Sonic some semblance of swimming ability, able to "swim" through the water in a buzzsaw like fashion and then speed through using the drill wisp power up with a mix of open spaces and tunnel like mazes for layouts. They lack the tension of Mega Drive Sonic water stages but they retain the sense of speed the series is known for, you could actually take this as a bad thing depending on how you feel about the older stages but regardless it was an interesting take on Sonic's water stages and took place in a memorable oriental setting with great music.
I'd add Hydrocity Zone from Sonic 3 as well but really half the reason that one is fun is because you spend a lot of time zipping across water, down slides and pushed along by currents so the water wasn't so strong a factor, once again this was keeping up the tempo while the slower sections allowed for bubble shields to retain air.
I see the original DKC pop up a bit when it comes to good water stages but I just don't really agree, they're okay but there's nothing about the levels themselves that shine in particular, a bunch of mostly cramped coral pathways focused primarily on evasion to the sounds of Aquatic Ambiance, it's that last point that I'm sure leads to the praise as it helps give them a relaxed atmosphere.
I found as with many things in this series that DKC2 was a lot more interesting with its water stages bringing gimmicks like that of Lava Lagoon leading to a frantic swim through boiling water that must be cooled by an animal buddy first and will reheat shortly alongside shifting water levels, that said they still could be a bit better on the whole.
Really though the crown for this series goes to Tropical Freeze which seemed to be inspired by the Rayman school of swimming while retaining Retro's own sense of weighty Ape factor to the character control, you're capable of fluid movements but need to get a handle on exactly how to move Kong around, each side character can assist with a water aid of their own. Level design is perhaps a bit too heavy on water currents at points and I imagine would improve further if there were another entry. They also used oxygen which some find a bit daunting, not me as that's one of the best things about the old Sonic water stages in my book.
Taking water stages on board as a whole I think you need to focus on making movement in the water enjoyable in itself while taking advantage of the benefits that come with the freedom of being able to move all around the immediate area, I also find mixing water and ground portions together works to alleviate some of the tedium that can come with spending too much time in the wet stuff.
I know a lot of people hated it, but I loved the underwater fights in Monster Hunter 3U for 3DS. I never played MH Tri on the Wii, which is where they were introduced. I could understand hate for them there, but on the 3DS the 3D gave you a great sense of situational awareness, I felt. Once I got the circle pad pro for it, I was in heaven. I am very sad to see them be removed in MH4..
Fine tuning these a bit further could have gone a long way, I like them in concept, monsters that reside in the depths gain an advantage with their superior moment and it brings a sense of dread that's just so fitting as well as joy when you get them onto land like there's an interesting back and forth dynamic dependent on where the fight takes place.. The upside for the hunters being you have more options in movement which aids in evading and attacking from different angles.
Problem I found was that the lack of being able to make a big evasive move like the running dive on land was no longer doable meaning that sometimes you'd just get utterly wrecked especially in cramped areas.
I'm sort of sad to see them gone whilst also being kinda thankful, I'd love to see them take another swing at it in the future.