In ScyllaDB, Leveled compaction (LCS) works very similarly to how it works in Cassandra and Rocksdb (with some minor differences).
If you want a short overview of how leveled compaction works in Scylla and why, I suggest you read my Write Amplification in Leveled Compaction blog post.
Your specific question on why two levels (L0 of recently flushed SSTables, Ln of disjoint-range SSTables) are not enough - is an excellent question:
The main problem is that a single flushed MemTable (SSTable in L0), containing a random collection of writes, will often intersect all of the SSTables in Ln. This means rewriting the entire database every time there’s a new MemTable flushed, and the result is a super-huge amount of write amplification, which is completely unacceptable.
One way to reduce this write amplification significantly (but perhaps not enough) is to introduce a cascade of intermediate levels, L0, L1, …, Ln. The end result is that we have L(n-1), which is 1/10th (say) the size of Ln, and we merge L(n-1) - not a single SSTable - into Ln. This is the approach that leveled compaction strategy (LCS) uses in all systems you mentioned.
A completely different approach could be not to merge a single SSTable into Ln, but rather try to collect a large amount of data first and only then merge it into Ln. We can’t just collect 1,000 tables in L0 because this would make reads very slow. Rather, to collect this large amount of data, one could use size-tiered compaction (STCS) inside L0. In other words, this approach is a “mix” of STCS and LCS with two “levels”: L0 uses STCS on new SSTables, and Ln contains a run of SSTables (SSTables with disjoint ranges). When L0 reaches 1/10th (say) the size of Ln, L0 is compacted into Ln. Such a mixed approach could have lower write amplification than LCS, but because most of the data is in a run in Ln, it would have the same low space and read amplifications as in LCS.
ScyllaDB implements this idea with Incremental Compaction Strategy (ICS). AFAIK none of the other mentioned databases ( Cassandra or Rocksdb) support such “mixed” compaction.
You can read more about ICS in the Maximizing Disk Utilization with Incremental Compaction blog post and in the Incremental Compaction 2.0: A Revolutionary Space and Write Optimized Compaction Strategy blog post.
*Based on an answer on Stack Overflow by Nadav Har’El