title: Calculating Median using Percentile on Redshift
body: <p>I have a large table with over 18M rows and I want to calculate Median and I am using PRECENTILE for that. However the time taken is around 17 minutes, which is not ideal.</p>
  
  <p>Here is my query</p>
  
  <pre><code>WITH raw_data AS
  (
    SELECT name AS series,
           (duration) /(60000) AS value
    FROM warehouse.table
  ),
  quartiles AS
  (
    SELECT series,
           value,
           PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY value) OVER (PARTITION BY series) AS q1,
           MEDIAN(value) OVER (PARTITION BY series) AS median,
           PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY value) OVER (PARTITION BY series) AS q3
    FROM raw_data
  )
  SELECT series,
         MIN(value) AS minimum,
         AVG(q1) AS q1,
         AVG(median) AS median,
         AVG(q3) AS q3,
         MAX(value) AS maximum
  FROM quartiles
  GROUP BY 1
  </code></pre>
  
  <p>Is there a way I could speed this up?</p>
  
  <p>Thanks</p>