Training rank against holdout result: 240 trials cut into fifths

WITH month_end AS (
    SELECT ticker,
           toStartOfMonth(toDate(toTimeZone(window_start, 'America/New_York'))) AS month_start,
           argMax(toFloat64(close), window_start) AS close_px
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('AAPL','ADBE','AMZN','BA','CAT','COST','CRM','CSCO','CVX','DE',
                     'DUK','GE','GOOGL','HD','HON','IBM','INTC','JNJ','JPM','KO',
                     'LMT','MCD','MMM','MRK','MSFT','NKE','NVDA','ORCL','PEP','PFE',
                     'PG','QCOM','SO','T','TGT','TXN','UNP','VZ','WMT','XOM')
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2015-12-01')
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2026-06-30')
      AND toDayOfMonth(toTimeZone(window_start, 'America/New_York')) >= 22
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY ticker, month_start
),
lagged AS (
    SELECT ticker,
           month_start,
           close_px,
           lagInFrame(close_px) OVER (PARTITION BY ticker ORDER BY month_start
                                      ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_px
    FROM month_end
),
monthly_return AS (
    SELECT ticker, month_start, close_px / prev_px - 1 AS ret
    FROM lagged
    WHERE prev_px > 0
      AND month_start >= toDate('2016-01-01')
),
trial AS (
    SELECT arrayJoin(range(1, 241)) AS n
),
factor_month AS (
    SELECT t.n AS trial_id,
           m.month_start AS month_start,
           avgIf(m.ret, bitAnd(cityHash64(m.ticker, toString(m.month_start), t.n), 1) = 1)
         - avgIf(m.ret, bitAnd(cityHash64(m.ticker, toString(m.month_start), t.n), 1) = 0) AS long_short_ret
    FROM monthly_return AS m
    CROSS JOIN trial AS t
    GROUP BY trial_id, month_start
    HAVING countIf(bitAnd(cityHash64(m.ticker, toString(m.month_start), t.n), 1) = 1) > 0
       AND countIf(bitAnd(cityHash64(m.ticker, toString(m.month_start), t.n), 1) = 0) > 0
),
scored AS (
    SELECT trial_id,
           avgIf(long_short_ret, month_start <  toDate('2021-07-01'))
             / stddevSampIf(long_short_ret, month_start <  toDate('2021-07-01')) * sqrt(12) AS in_sample_sharpe,
           avgIf(long_short_ret, month_start >= toDate('2021-07-01'))
             / stddevSampIf(long_short_ret, month_start >= toDate('2021-07-01')) * sqrt(12) AS out_of_sample_sharpe
    FROM factor_month
    GROUP BY trial_id
    HAVING countIf(month_start <  toDate('2021-07-01')) >= 24
       AND countIf(month_start >= toDate('2021-07-01')) >= 24
),
ranked AS (
    SELECT trial_id,
           in_sample_sharpe,
           out_of_sample_sharpe,
           row_number() OVER (ORDER BY in_sample_sharpe DESC) AS in_sample_rank
    FROM scored
)
SELECT multiIf(in_sample_rank <=  48, 'best fifth in training',
               in_sample_rank <=  96, 'second fifth',
               in_sample_rank <= 144, 'middle fifth',
               in_sample_rank <= 192, 'fourth fifth',
               'worst fifth in training') AS training_group,
       round(avg(in_sample_sharpe), 2) AS avg_in_sample_sharpe,
       round(avg(out_of_sample_sharpe), 2) AS avg_out_of_sample_sharpe
FROM ranked
GROUP BY training_group
ORDER BY min(in_sample_rank)