please check the following dataset
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 ?
Nest the aggregate in an OLAP function:
MAX(purchasedate) - MIN(purchasedate),
MAX(MAX(purchasedate)) OVER () - MAX(purchasedate)
GROUP BY 1
this works perfect. since I wanted to calculate deviation from absolute, I used -
(purchasedate)) OVER () -
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.