postgresql - SQL to work out sales by product taking into account age -

i want work out sales product grouped release date, grouped age of product when sold, this:

        | 3 months | 6 months     2015-01 | 28.1     | 37.1 2015-02 | 29.3     | 35.6 

so 28.1 average number of products sold of each type, 3 months after being released, products released in 2015-01. there more products sold 6 months after release date, 37.1.

the following sql gets list of sales:

select   d.item               title,   d.quantity,   a.firstdate          release_date,   i.date               invoice_date,   i.date - a.firstdate age  invoices   join invoice_details d on i.id = d.invoice_id    join (select           d.item,           d.binding,           min(i.date) firstdate         invoices           join invoice_details d on i.id = d.invoice_id         group d.item, d.binding) on a.item = d.item , a.binding = d.binding    i.discount != 100 , d.price > 0   , (d.binding != 'hardback' or d.binding != 'ebooks')  order title, invoice_date 

and result looks like:

title | quantity | release date | invoice date | age     | 1        | 2013-11-14   | 2013-11-14   | 0     | 2        | 2013-11-14   | 2013-12-14   | 30     | 3        | 2013-11-14   | 2014-01-14   | 60     | 4        | 2013-11-14   | 2014-02-14   | 90     | 5        | 2013-11-14   | 2014-03-14   | 120 b     | 6        | 2013-11-14   | 2013-11-14   | 0 b     | 7        | 2013-11-14   | 2013-12-14   | 30 b     | 8        | 2013-11-14   | 2014-01-14   | 60 b     | 9        | 2013-11-14   | 2014-02-14   | 90 b     | 10       | 2013-11-14   | 2014-03-14   | 120 

for product a, total sales 3 months after release date of 2013-11-14 1+2+3=6. product b, total sales 3 months after 6+7+8=21. average sales per title month of 2013-11, 3 months after (6+21)/2=13.5

for 6 months after it's ((1+2+3+4+5) + (6+7+8+9+10)) / 2 = 27.5

the release date first date product sold - joined sub-query for. there better way of doing it.

i tried averages across 3, 6, 12 , 24 months:

select   to_char(a.release_date, 'yyyy-mm') release_date,    avg(case when i.date - a.release_date < 92     d.quantity end)             three_months,    avg(case when i.date - a.release_date < 183     d.quantity end)             six_months,    avg(case when i.date - a.release_date < 365     d.quantity end)             twelve_months,    avg(case when i.date - a.release_date < 730     d.quantity end)             twentyfour_months  invoices   join invoice_details d on i.id = d.invoice_id    join (select           d.item,           d.binding,           min(i.date) release_date         invoices           join invoice_details d on i.id = d.invoice_id         group d.item, d.binding) on a.item = d.item , a.binding = d.binding    i.discount != 100 , d.price != 0   , (d.binding != 'hardback' or d.binding != 'ebooks')   group release_date order release_date desc 

obviously it's totally wrong because it's not grouping results title. it's giving me average items per order rather average items per title.

by way stuck on postgres 8.2.

if understand correctly, want:

select   to_char(date, 'yyyy-mm') release_date,   avg(case when age <  92 quantity else 0 end) three_months,   avg(case when age < 183 quantity else 0 end) six_months,   avg(case when age < 365 quantity else 0 end) twelve_months,   avg(case when age < 730 quantity else 0 end) twentyfour_months (   select d.item, d.quantity, (i.date - first_release.date) age, fr.date   invoice_details d   join (     select d.item, min(i.date) date     invoice_details d     join invoices on i.id = d.invoice_id     d.binding != 'hardback' , d.binding != 'ebooks'     group d.item) fr using (item)   join invoice on i.id = d.invoice_id   i.discount != 100 , d.price > 0) foo group release_date order release_date; 

this quite untested because can't remember when last touched 8.2 installation. version not have common table expressions or lateral joins, name 2 critical features in later releases have made rather more intuitive.

anyway, trick first calculate age of every invoice relative book release date every book sold, average out on various time periods. @ filters moved them , altered them ((d.binding != 'hardback' or d.binding != 'ebooks') not want).