PERCENTILE

Syntax

PERCENTILE(values, percentile) | APPROX_PERCENTILE(values, percentile)

AMC specifics

• The percentile parameter lies between 0 and 1. The input is numeric values.
• MEDIAN() returns the middle value of a set of numbers, so it matches the 50th percentile.

Example

Click cost per ad product. spend is in microcents, hence the division by 100,000,000.

WITH click_spend AS (
  SELECT
    ad_product_type,
    spend / 100000000 AS spend
  FROM
    sponsored_ads_traffic
  WHERE
    clicks > 0
    AND line_item_price_type = 'CPC'
)
SELECT
  ad_product_type,
  PERCENTILE(spend, 0.9) AS p90_click_cost,
  MEDIAN(spend) AS median_click_cost
FROM
  click_spend
GROUP BY
  1

Checked against the AMC schema, not executed on an AMC instance.