Caching derived dataset automatically in SQL Server
I could create a cache table and manually load it any time the underlying table if modified, but this feels like a hacky workaround. I think I'd also have to create a SQL Agent Job for this


That's the fallback.  And it's totally normal to run an ETL job after loading staging tables.

You might be able to do something with 2 indexed views instead of one.  And, of course, you can play with indexes and columnstore tables to improve query performance.