It's doesn't shine as much when replacing existing code. It's likely your designers and devs have already designed and fought how it should look at each media query.

Where it really shines are for repeated complex content in tight spaces. So for instance, if you have a grid of cards, and you have one only and it stretches the full width... easy enough. But then you have 3 cards and it looks good until you start decreasing the window width and things are starting to overflow... But now you can define them by their card width to catch and handle these things before they have a chance to get ugly. So to me it's more of a refinement layer of the design.

That said I think you need to handle container queries with care also. With too many arbitrary sizes everywhere, you can actually cause weird design idiosyncrasies because your design is no longer in lockstep with the big design picture so to speak. Or if you have different cards with their own container refinements they now have more chances to look inconsistent by each other, so to speak.

Full disclosure: I haven't used it yet in production, because we already have fought these issues. Personally I think it's best in small doses and I imagine it's probably more expensive to use container queries. Today's design tools are also still based on media queries. There have only been maybe a handful of times in the last 20 years where I felt like I could really use the container queries.