3,256 answered market questions

WITH retired AS
(
    SELECT
        ticker,
        max(date) AS last_session
    FROM global_markets.stocks_daily_aggs
    WHERE date >= '2018-01-01'
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker
    HAVING max(date) >= today() - 1130
       AND max(date) <  today() - 250
)
SELECT
    multiIf(
        dateDiff('day', d.date, r.last_session) <= 10,  'final 10 days',
        dateDiff('day', d.date, r.last_session) <= 30,  '11 to 30 days out',
        dateDiff('day', d.date, r.last_session) <= 60,  '31 to 60 days out',
        dateDiff('day', d.date, r.last_session) <= 120, '61 to 120 days out',
                                                        '121 to 250 days out')        AS wind_down_stage,
    round(quantileDeterministic(0.5)(
        toFloat64(d.high - d.low) / toFloat64(d.close) * 100,
        cityHash64(d.ticker, d.date)), 2)                                             AS median_daily_range_pct,
    round(quantileDeterministic(0.5)(
        toFloat64(d.volume) * toFloat64(d.close) / 1000,
        cityHash64(d.ticker, d.date)), 1)                                             AS median_dollar_volume_k
FROM global_markets.stocks_daily_aggs AS d
INNER JOIN retired AS r ON r.ticker = d.ticker
WHERE d.date >= today() - 1500
  AND d.date >  r.last_session - 250
  AND d.date <= r.last_session
  AND d.close > 0
GROUP BY wind_down_stage
ORDER BY min(dateDiff('day', d.date, r.last_session)) DESC