Reduce the number of queries to the same table
You can cut this down quite a bit - the first thing to notice is that you don't need the information in the author_titles table at all to get your results:

select author_id
     , sum(case max_sold when 1 then 30 when 2 then 60 when 3 then 100 else 0 end)/count(*)
         max_items_sold_wavg
     , sum(sold)/count(*) items_sold_wavg  
     , created_at
from ( select author_id
            , created_at
            , sum(case items_sold when 1 then 30 when 2 then 60 when 3 then 100 else 0 end)
                sold
            , max(items_sold) max_sold
       from author_reports
       group by author_id,title_id,created_at ) z
group by author_id,created_at;


SQLFiddle here