1

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.

1
  • 1
    I think window/analytic functions might help in this case. – Colin 't Hart May 19 '15 at 7:39
5

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.