Cassandra is a distributed database that easily scales more or less to infinity. As you add more nodes to a cluster, it automatically redistributes data throughout the cluster. It "just works", and that's pretty exciting. So Cassandra at the moment seems to be enjoying a bit of the hype cycle that MongoDB enjoyed 5 or 6 years ago. It's the thing that everyone wants to try out and get onto their resume at the moment.
However, like all "NoSQL" databases, the trade-offs come with the usual severe limitations (e.g. no joins, no foreign keys, no transactions in the usual sense of the term, eventual consistency, etc). Curious people diving into Cassandra (or most NoSQL databases for that matter) typically have zero understanding of these trade-offs at first, and think that they will be easier to work with. It isn't, and it isn't supposed to be. It's harder to work with, and has fewer features, because that's the trade-off you're making for scalability. There's little reason to use it if you don't need to scale like that.
So, curiosity and RDD ("resume-driven development") aside, I don't think there are many situations in which one would be choosing from Cassandra or MySQL/MariaDB/PostgreSQL. If you are working with multiple terabytes or more of data, then there is seldom a good reason to consider a relational database. If you are working with less than a terabyte of data, then there is seldom a good reason to consider anything OTHER than a relational database. I just don't see much overlap in which these technologies seriously compete against each other.