Example: Calculate the MEDIAN in BigQuery

calculate-median-bigquery-example.sql

# Calculate the MEDIAN in BigQuery; standard-sql; 2020-06-20
# @see http://www.pascallandau.com/bigquery-snippets/calculate-median/
WITH data as (
  SELECT
    1 as id,
    "2020-06-20" as day,
    10 as quantity
  UNION ALL SELECT 2,"2020-06-20", 15
  UNION ALL SELECT 1,"2020-06-21",5
  UNION ALL SELECT 2,"2020-06-21",10
  UNION ALL SELECT 1,"2020-06-22",9
  UNION ALL SELECT 2,"2020-06-22",4
),
median_per_row as (
  SELECT
    *,
    PERCENTILE_CONT(quantity, 0.5) OVER(PARTITION BY id) AS median,
  FROM data
)
SELECT
  id,
  ARRAY_AGG((SELECT as STRUCT day, quantity) ORDER BY quantity) as quantities,
  ANY_VALUE(median) as median,
FROM 
  median_per_row
GROUP BY
  id

Source: Calculate the MEDIAN in BigQuery