While Morrowind easily took the crown of "prettiest water" in 2002, Super Mario Sunshine on GameCube was good too, considering the more limited hardware compared to the PC. So, how did Nintendo manage it? By being very, very clever.
On a blog called "Clean Rinse", the effect has been deconstructed and recreated using WebGL2. If you can't see any of the demos embedded in the post, here are some GIFs showing things off.
Image: Clean Rinse
Along with using a bunch of overlapping textures to create a noisy water effect, Nintendo did something smart using mipmapping. I'll try to keep things simple, but basically, textures in games are usually a bunch of textures of various sizes, which the graphics API will transition between depending on how far away the camera is.
The primary reason mipmapping is done is to save on memory and improve performance: why sample high resolution textures for objects only a few pixels big on screen?
Nintendo was able to exploit this behaviour in an elegant way:
In the largest mipmap level (where the texture is closest to the camera), we don’t have any pixels. This basically removes the water effect in a small radius around the camera — letting the water be clear. This both prevents the water material from getting too repetitive, and also helps during gameplay by showing the player the stuff underwater that is closest to them.
The post goes on to mention that the third "bright" level gives the water its shininess, replicating the reflection of the sun on the water without doing expensive environmental lighting. It's clever as hell.
Of course, there's more to the water effect, so if you're curious, hit up the full post on Clean Rinse below.
Deconstructing the water effect in Super Mario Sunshine [Clean Rinse]