title: Drawdown AVERAGE and MEDIAN in SQL Server 2012
body: <p>I have a table 'TEMP' with columns 'Date', 'Ticker' and 'Price' :</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I have calculated daily maxprice and drawdown:</p>
  
  <pre><code>   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
  </code></pre>
  
  <p>How Do I calculate daily AVERAGE Drawdown and MEDIAN Drawdown?</p>
  
  <p>I can calculate it in Excel, but struggle with SQL code to produce this output:</p>
  
  <pre><code>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
  </code></pre>