The time you want water is when something that water can soak into is on fire. Sofas, curtains, bedding, bookshelves, carpets, filing cabinets full of paper, etc.

The water soaks in and prevents the fire re-igniting. 30 years ago, workplaces contained a lot more filing cabinets and bookshelves, and smoking was more common.

CO2 is non-conductive, and less messy. It's also great at flowing around things, making it good for spraying through vents into electrical cabinets and car engines. Downside is the CO2 dissipates within a few seconds, so if the material is prone to re-igniting it's not such a good choice.

Dry powder is in between - the powder stays around, but doesn't soak in. It's a good choice if you're only going to get one extinguisher.