I am unable to get the intended result table.
I have the following table:
ORDERSERIALNO USERID PRODSKU PRODUCTTABLE NETREVENUE ORDERPLACEDTIME
123-345 123 PHOTO01 CARDS 44 31OCT2010
123-454 123 PHOTO5x7 CARDS 30 23MAR2011
123-333 123 PHOTO8x10 CARDS 50 12OCT2011
123-897 123 IPHONE6 PAPERGIFT 40 6DEC2012
I want the result table as
ORDERSERIALNO PRODSKU PRODUCTTABLE NETREVENUE ORDERPLACEDTIME REVENUE_BEFORE1YEAR
123-345 PHOTO01 CARDS 44 31OCT2010 0
123-454 PHOTO5x7 CARDS 30 23MAR2011 44
123-333 PHOTO8x10 CARDS 50 12OCT2011 74(44+30)
123-897 IPHONE6 PAPERGIFT 40 6DEC2012 0
I want the result table to have a column of revenue generated 1 YEAR ago for all the orders before that particular product was purchased. This should be based on product table.
For example the 1st record has that field as 0 since no product was ordered before that. The 2nd record has the revenue of 1st record, since 1st record was purchased in the timespan of 1 year before the 2nd record. Similarly the 3rd records contains the revenue (summation of 1st and 2nd record) as they fall in the timespan of 1 year before the 3rd record.
But the 4th record has 0 in it because no PAPERGIFT product was ordered before the 4th record. The 1st,2nd and 3rd records have PRODUCTTABLE as CARDS, so the revenue was summed.
Hence i want this to be based on the product table and the time span.
I have written the following query but it does not produce the correct output :
select orderserialno, prodsku, netrevenue, orderplacedtime,
sum(netrevenue) OVER (PARTITION BY producttable, orderplacedtime - interval '365' day ROWS UNBOUNDED PRECEDING) AS REVENUE_BEFORE1YEAR
from scratch.net_revenue where userid ='001042874727'
Any corrections in the above query to get the correct output will be appreciated.