Reduce the number of queries to the same table I need to build some reports using two tables:

AuthorTitles: id(pk), author_id, title_id
AuthorReports: id(pk), author_id, title_id, items_sold, date


My report needs to output the fallowing: author_id, max_items_sold_wavg, items_sold_wavg, date

The below code might have some errors, please check the fiddle.

max_items_sold_wavg is the weighted average of the fallowing query:

WITH maxitems AS ( 
 SELECT
   at.author_id,
   at.title_id,
   max(ar.items_sold) as sold,
   ar.date::date as date
 FROM author_titles at
 LEFT JOIN author_reports ar ON ar.author_id = at.author_id AND at.title_id = ar.title_id
 GROUP BY 1, 2, 4
), totitems AS (
    SELECT
      mi.author_id
      count(ti.title_id) as total,
      count(CASE WHEN (mi.sold = 1) THEN mi.title_id END) AS sold1,
      count(CASE WHEN (mi.sold = 2) THEN mi.title_id END) AS sold2,
      count(CASE WHEN (mi.sold = 3) THEN mi.title_id END) AS sold3,
      mi.date,
    FROM maxitems mi
    GROUP BY mi.date
), itemsold AS (
   SELECT
      at.author_id
      count(at.title_id) as total,
      count(CASE WHEN (ar.items_sold = 1) THEN ar.title_id END) AS sold1,
      count(CASE WHEN (ar.items_sold = 2) THEN ar.title_id END) AS sold2,
      count(CASE WHEN (ar.items_sold = 3) THEN ar.title_id END) AS sold3,
      ar.date::date as date,
    FROM author_titles at
    LEFT JOIN author_reports ar ON ar.author_id = at.author_id AND at.title_id = ar.title_id
    GROUP BY at.author_id, ar.date::date
)
SELECT
  ti.author_id,
  ((ti.sold1 * 30 + ti.sold2 * 60 + ti.sold3 * 100) / at.title_id),
  ((is.sold1 * 30 + is.sold2 * 60 + is.sold3 * 100) / at.title_id),
  ti.date
FROM totitems ti, itemsold is
WHERE ti.author_id = is.author_id AND ti.date = is.date


You can notice that in maxitems I extract only the max sold for every title and in itemsold I extract all the sells for all the titles, because an author can sell many titles in one day.

Example report data:

http://sqlfiddle.com/#!15/d2efc/22

How can I improve this query, is there a better way of doing it, because you can see that I am accessing the same data multiple times.
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