2,309 answered market questions

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
)
SELECT concat('trial ', toString(trial_id)) AS factor_label,
       round(in_sample_sharpe, 2) AS in_sample_sharpe,
       round(out_of_sample_sharpe, 2) AS out_of_sample_sharpe
FROM scored
ORDER BY in_sample_sharpe DESC
LIMIT 12