Thank you @"mathguy" @manik for all your previous help !
I'm able to calculate the 1 year return value as expected per the sql updated below,need help with a sql to calculate the percentile and the value .
for a investment type group and date (this is usually quarterly )and no.of years(1 year,3year ). like i mentioned that was my final sql to be added for analytics.
I checked few fucntions, but clubbing that with my return calc is not working appropriately.
I need to consider and come up with percentile value for tickerids within a invgrp as of date, and for no of years in below format in my table.
date(quarterend) |invgrp|percentile|Timerange|value| along with rank if possible ! Please help the usage of the percentile functions for this aggregation logic.
So the percentile would be 0,0.05,0.25,0.5,0.75,0.95,1 . and corresponding percentile value.
I have looked at the percentile cont and percentile disc functions, but could not apply this to my previous calculated annualized retiurn sql below. which is for a particular tkrid .
select (exp ( 12 * AVG( ln ( 1 + MM_RTN_AT/100 ) ) )/100) as one_yr_ROR,
(exp ( 36 * AVG( ln ( 1 + MM_RTN_AT/100 ) ) )/100) as three_yr_ROR,
(exp ( 84 * AVG( ln ( 1 + MM_RTN_AT/100 ) ) )/100) as seven_yr_ROR
from
MM_RTN where tkr_id=1 and MM_BG_DT>='2016-01-01'
ONE_YR_ROR, THREE_YR_ROR, SEVEN_YR_ROR
0.0100244012606246, 0.0100733825536203, 0.0101720643071537