Is columnstore indexes not a good fit for this for performance for another query
Thank you in advance.
For a fair comparison, you need to fix the cardinality estimates for the grouping operations (hidden in a function?) so that the optimizer does not choose row mode partial and global aggregation (a reasonable enough strategy if the estimates were accurate).
You would also need to ensure the server has enough memory (and the query is able to access it) to handle the hashing operations. Currently the query is being granted the maximum allowed per query, meaning you need to either add memory or reconfigure workspace memory maximum grants.
As it is, the columnstore plan has many several expensive transitions between row and batch mode, and a couple of very expensive row mode spills.
The query itself may or may not be a great candidate for columnstore processing. If the aggregation operations do not reduce row counts very much, and the hash joins are large and similar on both inputs, the alternate strategy may make more sense. Only apples-to-apples testing would prove this one way or the other.