One of the most helpful usages of CC so far is when I simply ask: "Are there any bugs in the current diff" It analyzes the changes very thoroughly, often finds very subtle bugs that would cost hours of time/deployments down the line, and points out a bunch of things to think through for correctness.
My experience is that it is about 10% harmful, 80% useless and 10% helpful. Which is actually great, the 10% is worth it, but it is far from a hands off experience.
By harmful I mean something like suggesting a wrong fix to code that works, it usually happens when I am doing something unusual or counter intuitive, for example having a function "decrease_x" that (correctly) adds 1 to x. It may hint for better documentation, but you have to be careful not to go on autopilot and just do what it says.
By useless I mean something like "you didn't check for null" even though the variable can't be null or is passed to a function that handles the "null" case gracefully. In general, it tends to be overly defensive and following the recommendations would lead to bloated code.
By helpful I mean finding a real bug. Most of them minor, but for some, I am glad I did that check.
LLMs complement traditional linters well, but they don't replace them.