Creating a summary table for an averaging query
Here is a possible scheme:


Add a column to the activity_scores table is_processed which flags whether the row has been summarized into the summary table
Add a partial index to the activity_scores table for only the unprocessed rows, this could include all columns
Now when you go to load yesterday's data you are only looking through rows which you know have not been processed
Identify yesterday's rows as part of the load process and insert them into a temp table, then insert the summary of the rows into the summary table
Join the temp table to the source table on id and mark the rows as processed (should be quick as you have the ids and aren't doing a scan)