All Forums General
suyog 10 posts Joined 03/07
25 Jun 2013
calculate recency & absolute deviation for a dataset

Hi,
please check the following dataset
customerid  purchasedate 
1                      5/9/2013
1                      5/20/2013   
1                       5/29/2013
2                       6/8/2013
 
I need to calculate resultset like
"customerid"  "recency (max date - min date i.e. #days)"    "deviation from last purchase date in dataset"
1                     20 (5/29/2013 - 5/9/2013)                              30 (6/8/2013 - 5/9/2013)
 
I can't use max (date) here as it will always give me for the grouping column & not teh abslute max date. is there a way to achieve this ?

dnoeth 4628 posts Joined 11/04
25 Jun 2013

Nest the aggregate in an OLAP function:

SELECT
   customerid,
   MAX(purchasedate) - MIN(purchasedate),
   MAX(MAX(purchasedate)) OVER () - MAX(purchasedate) 
FROM tab
GROUP BY 1

Dieter
 

Dieter

suyog 10 posts Joined 03/07
26 Jun 2013

Thanks Dieter,
 
this works perfect.  since I wanted to calculate deviation from absolute, I used -
 
MAX(MAX(purchasedate)) OVER () - MAX(purchasedate)
If I understand correct, the use of window function over() overrides and considers the entire dataset obtained from teh table tab, right ?
Thanks again for helping me out.

You must sign in to leave a comment.