Glassdoor (Google) — running median по выручке

Условие

Таблица daily_revenue(date, revenue). Для каждой даты посчитайте бегущую медиану выручки за всё время с начала до этой даты включительно.

Решение

Подход — PERCENTILE_CONT в коррелированном подзапросе

SELECT
  d1.date,
  d1.revenue,
  (
    SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY d2.revenue)
    FROM daily_revenue d2
    WHERE d2.date <= d1.date
  ) AS running_median
FROM daily_revenue d1
ORDER BY d1.date;

Аккуратно: O(N²) — тяжёлая операция на больших данных.

Подход через NTH_VALUE / window (приближение)

Постгресовские window функции не поддерживают PERCENTILE_CONT OVER. Костыль через ручную медиану:

WITH ranked AS (
  SELECT
    date,
    revenue,
    ROW_NUMBER() OVER (ORDER BY date) AS rn_time
  FROM daily_revenue
)
SELECT
  r1.date,
  AVG(r1.revenue) -- средняя двух центральных элементов «to-date»
  ...

В реальности — это уже непросто без PERCENTILE_CONT в window.

Если разрешено BigQuery / Snowflake

В современных движках есть PERCENTILE_CONT(... ) OVER (ORDER BY ... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Тогда:

SELECT
  date,
  revenue,
  PERCENTILE_CONT(revenue, 0.5) OVER (
    ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_median
FROM daily_revenue;

Подводные камни

1. PERCENTILE_CONT vs DISC. CONT интерполирует между двумя центральными значениями (классическая медиана). DISC берёт ближайшее. Для собеса обычно CONT.
2. Производительность. Коррелированный подзапрос — O(N²). На реальных данных подсчёт пересчитывают инкрементально или используют t-digest.
3. Точное определение медианы. Чётное N — среднее двух центральных. Если бизнес хочет «нижнюю медиану» — это PERCENTILE_DISC(0.5).

Эталонный ответ

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY revenue) в коррелированном подзапросе с WHERE d2.date <= d1.date. На BigQuery — PERCENTILE_CONT OVER с фреймом UNBOUNDED PRECEDING.