I believe the daily version of the query can be optimized by moving the filter up in the subquery.
Before: kmV3 : Discourse Post.calculate_avg_time daily | explain.depesz.com
After: i3JU : Discourse Post.calculate_avg_time daily Optimized | explain.depesz.com
This would not apply to the weekly one, but as you stated the daily one is the bugger.
Can you try that and confirm the differences @michaeld ?