I totally agree that this is a gap in the Databricks solution.  This gap exists between a static read and real time streaming.   My problem (and suspect there are many use cases) is that I have slowly changing data coming into structured folders via csv that are updated monthly.    Most of the documentation is either for a static read of that or overly complex stream processing using watermarks, batch processing, etc in minutes, seconds, or real time.   

With that said, here are a few thoughts for you:

1. DBRX medallion architecture and DLT provides a useful model where you use autoloader to incrementally stream to a delta table.  I have found that if you don't do too much transform to it, it makes it easier to do subsequent transforms or aggregations in the subsequent silver or gold tables.
2. you are correct there are many limitations for what you can do with streaming aggregations.  But, I have found that one can do a LOT with streaming aggregations using groupby.agg.   Yes, you are limited with distinct counts but counts and other aggs work.  Rather than count distinct, you can use apporoximate_count_distinct which may work fine for some workflows.     I use approximate_count_distinct and then use a test process to ensure that the count is withing some tolerance level (ie. 10%).   
3. I have found you can do most anything you need on streaming transforms.   And dlt takes care of state processing for you.
4. Untested thoughts:   I believe there may be some ways to do this using either watermarking (my watermark would be day or month, not "10 minutes" though.   Or doing this using forEach or forEachBatch processing where you basically write a sink handler for each batch.   The idempotent table writes are interesting to me (subset of foreachBatch).  Perhaps that may be the key?  https://docs.databricks.com/en/structured-streaming/delta-lake.html#idempotent-table-writes-in-forea...
5. To your idea of building your own checkpoint logic -- Although DBRX cautions against using for loops as this defeats the purpose of parallel processing, I've considered building (have not tested this) my own state machine such that:
   1. For the source data, I filter it on each window and then write to my delta table if that window name doesn't exist in the table  (ie. window1, window2, window3).   
   2. This could possibly be done in conjunction with item 4?
6. The materialized views are a way in the medallion architecture to do any aggregation on a streaming table.   Just stream it to bronze level table when you are loading it.  Then read it (vs. readStream) and do your aggs.   This rebuilds the agg each time but this is in background.    

Just my two cents.

Lee