Drawdown AVERAGE and MEDIAN in SQL Server 2012

Question

I have a table 'TEMP' with columns 'Date', 'Ticker' and 'Price' :

Date       Ticker   price
01/01/13    ABC     100.00 
01/02/13    ABC     101.50 
01/03/13    ABC      99.80 
01/04/13    ABC      95.50 
01/05/13    ABC      78.00 
01/01/13    JKL      34.57 
01/02/13    JKL      33.99 
01/03/13    JKL      31.85 
01/04/13    JKL      30.11 
01/05/13    JKL      35.00 
01/01/13    XYZ      11.50 
01/02/13    XYZ      12.10 
01/03/13    XYZ      17.15 
01/04/13    XYZ      14.10 
01/05/13    XYZ      15.55

I have calculated daily maxprice and drawdown:

   select t.Date,
          t.Ticker,
          t.price, 
       max(t.price) over (partition by ticker order by date) as max_price,
       (t.price / max(t.price) over (partition by ticker order by date)) - 1 as Drawdown
  FROM [dbo].[temp] t;

Date    Ticker   price  max_price   Drawdown
01/01/13    ABC  100.00  100.00     0.000
01/02/13    ABC  101.50  101.50     0.000
01/03/13    ABC   99.80  101.50    -0.017
01/04/13    ABC   95.50  101.50    -0.059
01/05/13    ABC   78.00  101.50    -0.232
01/01/13    JKL   34.57   34.57     0.000
01/02/13    JKL   33.99   34.57    -0.017
01/03/13    JKL   31.85   34.57    -0.079
01/04/13    JKL   30.11   34.57    -0.129
01/05/13    JKL   35.00   35.00     0.000
01/01/13    XYZ   11.50   11.50     0.000
01/02/13    XYZ   12.10   12.10     0.000
01/03/13    XYZ   17.15   17.15     0.000
01/04/13    XYZ   14.10   17.15    -0.178
01/05/13    XYZ   15.55   17.15    -0.093

How Do I calculate daily AVERAGE Drawdown and MEDIAN Drawdown?

I can calculate it in Excel, but struggle with SQL code to produce this output:

Date       Average  Median
01/01/13     0.000   0.000
01/02/13    -0.006   0.000
01/03/13    -0.032  -0.017
01/04/13    -0.122  -0.129
01/05/13    -0.108  -0.093

Answer 1

You can use PERCENTILE_CONT function to find median easily. Here is a sample query, I have used your query as subquery

declare @t table (
    Date date
    , Ticker varchar(10)
    , price decimal(10, 2)
)

insert into @t values
('20130101', 'ABC', '100.00')
,('20130102', 'ABC', '101.50')
,('20130103', 'ABC', '99.80')
,('20130104', 'ABC', '95.50')
,('20130105', 'ABC', '78.00')
,('20130101', 'JKL', '34.57')
,('20130102', 'JKL', '33.99')
,('20130103', 'JKL', '31.85')
,('20130104', 'JKL', '30.11')
,('20130105', 'JKL', '35.00')
,('20130101', 'XYZ', '11.50')
,('20130102', 'XYZ', '12.10')
,('20130103', 'XYZ', '17.15')
,('20130104', 'XYZ', '14.10')
,('20130105', 'XYZ', '15.55')

select
    *, Average = cast(avg(Drawdown) over (partition by date) as decimal(10, 3))
    , Median = cast(percentile_cont(.5) within group (order by Drawdown) over (partition by date) as decimal(10, 3))
from (
    select 
        t.Date,
        t.Ticker,
        t.price, 
        max(t.price) over (partition by ticker order by date) as max_price,
        (t.price / max(t.price) over (partition by ticker order by date)) - 1 as Drawdown
    FROM @t t
) t