Water loss is not the issue, because you need a lot of water to make it work at all. Even if your dams lose 30% of their water every year, that's about 0.1% per day. System losses in the best large-scale systems are about 20%. In other words, 70-80% efficiency of large scale systems (wikipedia). The big issue, though, is likely to be making it work at all. Conveniently that wikipedia article has a small-scale example:
1000 kilograms of water (1 cubic meter) at the top of a 100 meter tower has a potential energy of about 0.272 kW·h
To make it useful you'd want to store as much energy as a modern small battery system, say 10kWh. Assuming your "house" is 100m tall (25-30 storeys), you get 100m of head and using the 0.27kWh/m³, 10 kWh is about 40m³ (40,000 litres) of storage. That's a small farm dam or big farm tank, it's definitely not a rainwater tank as most urban households think of it. Here's a pair of 38,000 litre tanks sitting in front of a house:
The other problem is getting an efficient pump that can push water from your bottom 40m³ dam to the top 40m³ dam. Amazon will sell you a 2kW pump capable of 3m/hr to 100m for $1500. Efficiency is not very high - that's 2kWh input for 0.8kWh stored (3x0.272kWh from above). To get from 40% efficient to 90% efficient is going to cost a lot more, and if you're not 90% efficient on the storing side, you're going to struggle to get 80% round-trip efficiency.
Note that you can trade head for volume linearly, but the efficiency of your pipes, pumps and turbines is crucial. The reason moderate head systems are preferred is that it's easier to get them up to the efficiency required. If you have 1000 metres, or 10 metres, of head, it becomes very difficult to make the system efficient. For example, most bore pumps with over 100m of head are simply multistage versions of lower-head pumps, so the efficiency drops even further. But flow turbines for low-head systems usually peak at 70%-80% efficient rather than the 90% you can get out of a pelton wheel:
(via wikidot - relative efficiency of various turbines as flow rate varies as a proportion of rated)
So this is not something you can do at all as an urban household, and it will be difficult for most rural landowners.
When I had a microhydro system on a property I owned, I had about 20 litres/second available but the head was over 100m because the block of land was steep (hence, cheap :)). The main limitation was my willingness to drag heavy pipe up a steep hill. Vehicle access to the top of the block was strictly "all wheel drive with winch" and took a whole day - that sort of terrain is brilliant for hydro, but steep land with high rainfall is a disaster for ground vehicles.
Because of the high rainfall, the "reservoir" at the top of that pipe was just an inlet filter anchored to bedrock using a cubic metre of concrete, there wasn't a dam per se, let alone storage. Getting the top anchor in place was over a months work for one man using machinery. Getting a tank like the one above to the top would have needed a helicopter. A big, expensive helicopter that would have had to come from a long way away. A dam would be easier, but still ridiculously difficult.
