Efficiently store time based data
You might look into using materialized views, if the partitions become effectively read-only on a daily basis. You can pre-calculate the results of these queries for the older data in the materialized views.