Science

It's a big battery. Useful in all cases.

They literally do this somewhere. There's a Tom Scott video about it.

It seems like you'd lose a fair bit of energy from powering the pump and from the turbine not capturing 100% but I don't actually know how it compares to other types of energy storage.

I think hydro power storage is a good idea where it can be done (and especially where you have existing hydro power), but most places don't have a giant reservoir nearby that could be used for this purpose. Something that I think could be a better option for widespread grid storage are flow batteries. These work by basically pumping an electrolyte solution back and forth across a membrane to store or discharge energy. They aren't as energy dense as lithium batteries, but they have some advantages like being able to be used indefinitely without storage capacity degradation (though you do have to do upkeep on the pumps and whatever else). They're already used in places like hospitals and for grid storage in China. These current flow batteries use vanadium, but there's research being done on flow batteries that use iron instead, which is cheap and abundant in comparison. Short video on that: https://youtu.be/mu6mZZuPdpI

How much energy is generated versus how much is used to pump water back up?

Recent forays into trying to do more pumped hydro in the US: https://www.hydroreview.com/hydro-industry-news/pumped-storage-hydro/alabama-power-files-with-ferc-to-surrender-chandler-mountain-pumped-storage-permit/