Thanks for your question, I’ve been trying to think of the best way to answer it for a while, so here goes!
Sugar is broken down to create glucose, and then ATP, the energy needed in cells. So once the sugar you’ve consumed is broken down, then no more energy can be created, so the effect wears off.
Caffeine is a little more complicated. When you drink coffee, the moceules of caffeine can get from your blood in to your brain. Here they join with certain receptors (bits in a cell membrane which can transport material, or send messages to other cells). These receptors release a chemical which increases alertness. Also when caffeine is broken down, some of the smaller molecules also cause alertness and increase heart rate (which is why sometimes if you drink too much coffee you feel your heart fluttering or pounding! I do, anyway!). When these get broken down even more, the effect of caffeine wears off, and they disperse in the blood.