I'm reminded of Ziferblat [1] and other similar "anti-cafes" [2]. They shut down their local one here though, apparently due to not being able to meet the cost of skyrocketing commercial rents.
[1] https://www.ziferblat.co.uk/Coventry/discover [2] https://en.wikipedia.org/wiki/Anti-caf%C3%A9
The usual killer, as you've identified, isn't people sitting at the tables but the rents. Possibly you could do a quite "successful" cafe as a non-profit that owns the building it is sitting in.
Also it seemed the more people who where actually there the more business it received.
Because real estate prices and thus rent have gone through the roof compared to the historic average.
Coffee used to be expensive relative to wages and rent ultra-cheap. Now that's flipped.