Where does this magic cable go?

The answer is either "it goes on the threaded port of your cable modem" or "it goes to a distribution panel somewhere outside". It really depends what you meant by the question.

How long is the cable?

Normally you want to keep the cable as short as possible.

How does so much data go through a single-pin coax cable?

Technology has continued to progress but I think many cable providers are capping at around 100 mbps. I could be wrong.

Wouldn't it be better if there were more pins, like in a twinax configuration?

Not necessarily.

There are also other houses in my neighborhood. Are their cables connected to mine?

It depends on the configuration your ISP used. Many would in fact share a pipe's bandwidth amongst blocks of homes. I not sure how prevalent that practice is today.

Can their routers see the packets sent by my router, similar to ethernet?

No. Every single home is on a different network.

How has your day been?

It's almost 1:00pm and I've been so busy that I haven't had a chance to have breakfast yet.