choppy clear water with beautifully distorted tile floor underneath. The works. I have no idea how he did all that
Really easy when you know how, it's all about getting to know your tools. Knocked this up in 5 minutes just to demonstrate:

Click image for larger version.  Name: water.jpg  Views: 118  Size: 128.3 KB  ID: 61103

You wont get the colour right just by selecting a red, as the Difference Cloud will mess your colour up, so just change the hue once you have the pattern you want. For more rough water make your waves more accentuated, but for water that's barely moving then throw on some faint ripples.

I agree with what Gamer says about the water (literally looks like a mini ocean in your map), but as often as not you'll want to have SOME ripples even in dead water otherwise it can be quite hard to make it look like water (possible, but adding faint ripples is a lot easier).