Obviously this depends on two things – if the water is infected with the bacteria, and if you and said bacteria get in close enough contact.
On average in the developed world (Europe, mainland USA, etc) about 20% of all rats carry strains of leptospira that could cause illness in humans. This of course varies locally – in your area you may have 100% rat carriers, or 0%. It just depends on the social lives of the rats in question. It’s therefore sensible to assume that on average 20% of all open water sites are infectious, with more probability for sites which stand good chances of hosting rats nearby (urban ponds, slow-moving rivers and canals, lakes near farm buildings, etc) and less of a risk for non-rodent-friendly sites such as rapid flowing highland streams. Obviously any site with a high water thorughput (such as a river) is less of a risk than stagnant water, as rodent urine will be less concentrated. Any water treated with chlorine or UV-sterilisation will be totally safe. This means that swimming pools, and many municipal water fountains and features, are usually no risk.
The chances that being exposed to contaminated water would lead to infection depends on what you do in the water. To become infected you must actually allow water to enter your body, though that could be as simple as through an open cut, or by licking a finger. Swimming is the highest risk activity as there is no way to prevent some ingestion and skin contact, though other activities such as fishing, skiing, sailing and kayaking can also present risk. Remember that the bacteria cannot survive in saltwater.
In general in the developed world people are wary of open water sites from general cleanliness viewpoints, and would not drink from a lake without a very good reason. The chances of infection are therefore quite low, but these statistics hide the fact that in many cases the infection is mild, and goes unreported. Despite only a few hundred cases being reported in each European country each year, there may well be several times more cases which are simply written off as a cold or stomach bug.
In developing countries the risks are greater, as rat populations are more widespread and water use is different. The quantity of untreated water used for washing, bathing and drinking is far higher, and the association between hygiene risks and open water is rarely made. It is for these reasons that leptospirosis is the second most widespread zoonotic infection in the world, surpassed only by malaria. Education in developing countries is the only solution to this issue, as the bacteria and the rats are there to stay.