Are there any special places or things that this can be used to find, or is it just the assorted loot here and there?

Go and find Pilgrim's Trench.

Ive only found one sunken ship in the wilderness so far.

There is one dungeon I went into yesterday that I could have used Waterbreathing in. However, there was an alternative path. I have seen some pools in caves that might be worth a dive.

However, I have yet to see a place in the game where it is required. Then again, I have only been in about 30 caves and I have not done much coastal exploring.

There are some lakes and rivers and dungeon pools with chests or skeletons with some equipment underwater, but nothing you can't get to just by holding your breath. I have never seen any place in the game with a 'get Water Breathing or die trying' requirement. Kind of a shame, but the problem with water is that you cannot fight in it, so a prolonged underwater area would have to be purely exploration based.

I have finally finished the game. I have explored almost all locations on the map and I have only found one place where it wasn't enough to hold your breath. It's a spot in the northern waters where there are a lot of sunken ships.

I have sometimes found it useful to dive below the surface to escape enemies or just use it as a cover to sneak up on them.

I'll try to avoid spoilers:
In a certain questline, you have to kill a certain someone and escape from a certain dungeon while it's flooding with water. Argonian would be useful if you don't know how to get out and want to search for an exit safely.
If you want the spoiler then here:

Not really, since the effect is replicable via a helmet enchantment, though it's so situational that you may as well just use potions if any area really needs it.

The High Elf buff is really useful; it's 5 levels worth of free stat upgrades in magic. Orc berserker rage is also awesome.

you don't drown

the end....

Yea, I got a bit disoriented there, happened to have a water breathing helmet I dragged about on me that saved my rear. In the end the water breathing is easily replaceable.

He he, I also got disoriented in that area. Or... I think I tried to find the exit too quickly and when I couldn't find it I tried to exit through the pipes, but they were just there for show... Luckily I had a waterbreathing helmet to switch to when I started taking damage. ^_^

for the times you need it, there are the potions.