Best way to optimize an aggregated issues over time query?
Save the aggregation in a &quot;summary table&quot;.  Then add a new row(s) each night.  The 'report' would sum the sums and sum the counts.  (Average would be (sum of sums) / (sum of counts).)
I'm confused.  You seem to have a date in the Fact table, yet you want to summarize for one day?  Or a range of days?  Please provide SHOW CREATE TABLE and say approximately how big the tables are.
More discussion:  http://mysql.rjweb.org/doc.php/summarytables