> However, I'm wondering if SQL is the right tool for the task. For instance, the listing 2 seems complex when compared to the query expressed in plain English
For me Listing 2 is more complex and less intuitive than Listing 1 (CQL). Yet, it is a general problem when we try to adopt relational (and SQL) concepts for solving such kind of tasks (windowing, grouping, aggregation etc.) One solution is to switch to some kind of column algebra rather than relational algebra as described in [1] and [2] (it has also been applied to stream processing).
For me Listing 2 is more complex and less intuitive than Listing 1 (CQL). Yet, it is a general problem when we try to adopt relational (and SQL) concepts for solving such kind of tasks (windowing, grouping, aggregation etc.) One solution is to switch to some kind of column algebra rather than relational algebra as described in [1] and [2] (it has also been applied to stream processing).
[1] Joins vs. Links or Relational Join Considered Harmful: https://www.researchgate.net/publication/301764816_Joins_vs_...
[2] From Group-By to Accumulation: Data Aggregation Revisited: https://www.researchgate.net/publication/316551218_From_Grou...