Generally speaking, you're probably correct, and yet for some individuals some of the time some amount of alcohol, caffeine, psilocybin, mdma, etc etc etc, may have a beneficial effect for some period of time.
https://maps.org/research/mdma
https://hopkinspsychedelic.org
In other words, these medicines are being explored specifically through the lens of public health policy. The question of their benefits being a statistical anomaly (when used in appropriate settings) is looking increasingly unlikely.
Great username btw
Honestly, if I'm truly tired I can go to sleep immediately after drinking a cup. I don't generally intend to do this, obviously, but I've never felt like coffee prevented me from sleeping. It gives me clarity and makes me feel invigorated, but it doesn't feel like the opposite of sleep.
This is highly individual. There are people who can go to sleep after drinking a cup of coffee, who feel sleepy after drinking small amount (how small actually varies) and then people like me that can go on for 5 more hours no matter how tired, even after being awake for more than 24 hr - there is no dice roll, works 100 %.