Sum of previous rows based on time intervals

Database

Sum of previous rows based on time intervals

Hi,

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. 

Thank you.

1 REPLY
Enthusiast

Re: Sum of previous rows based on time intervals

Sum(netrevenue)over(partition by producttable order by orderplacedtime rows unbounded preceding)

try this