(TimescaleDB engineer) we hear you and are working on making continuous aggregations easier to use.
For now, the recommended approach is to perform continuous_aggregates on single tables and perform joins, order by, and window when querying the materialized aggregate rather than when materializing.
This often has the added benefit of often making the materialization more general so that a wider range of queries can use it.
Thanks for the advice! Makes sense. We are doing something similar (aggregate on single table and join later). But still looking a solution to compute aggregated increments when there are counter resets.
For now, the recommended approach is to perform continuous_aggregates on single tables and perform joins, order by, and window when querying the materialized aggregate rather than when materializing.
This often has the added benefit of often making the materialization more general so that a wider range of queries can use it.