title: BigQuery Standard SQL: How to get median of difference between two columns?
body: <p>How can I get the median difference after subtracting two columns in a table after I set the dialect to <code>Standard SQL</code>?</p>
  
  <p>These are the things that I have tried and failed with:</p>
  
  <pre><code>SELECT
    symbol,
    median(max_height-min_height)
  FROM
    `body_table-1345.bodies.heights`
  WHERE
    symbol = 'mouse_1344'
    AND max_height&gt; 0
    AND min_height&gt; 0
    AND (measure_date&gt; '2017-11-01 08:45:00.000 UTC'
      AND measure_date&lt; '2017-11-30 17:00:00.000 UTC')
  GROUP BY
    symbol
  </code></pre>
  
  <p>but I get the error <code>Error: Function not found: median at [3:3]</code></p>
  
  <p>The following works with <code>Legacy SQL</code> but not with <code>Standard SQL</code>:</p>
  
  <pre><code>SELECT
    symbol,
    NTH(501, QUANTILES(max_height-min_height, 1001))
  FROM
    `body_table-1345.bodies.heights`
  WHERE
    symbol = 'mouse_1344'
    AND max_height&gt; 0
    AND min_height&gt; 0
    AND (measure_date&gt; '2017-11-01 08:45:00.000 UTC'
      AND measure_date&lt; '2017-11-30 17:00:00.000 UTC')
  GROUP BY
    symbol
  </code></pre>
  
  <p>The columns I'd like to subtract are <code>max_height</code> and <code>min_height</code>.</p>