LLM ఆల్ఫా ఫ్యాక్టర్లను కనుగొనగలదా?

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('2021-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,
           avg(long_short_ret) / stddevSamp(long_short_ret) * sqrt(12) AS sharpe
    FROM factor_month
    GROUP BY trial_id
    HAVING stddevSamp(long_short_ret) > 0
)
SELECT multiIf(sharpe < -1.2, 'below -1.2',
               sharpe < -0.8, '-1.2 to -0.8',
               sharpe < -0.4, '-0.8 to -0.4',
               sharpe <  0.0, '-0.4 to 0.0',
               sharpe <  0.4, '0.0 to 0.4',
               sharpe <  0.8, '0.4 to 0.8',
               sharpe <  1.2, '0.8 to 1.2',
               '1.2 and above') AS sharpe_bucket,
       count() AS factor_count,
       round(100 * count() / 240, 1) AS share_pct
FROM scored
GROUP BY sharpe_bucket
ORDER BY min(sharpe)

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('2021-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,
           avg(long_short_ret) / stddevSamp(long_short_ret) * sqrt(12) AS sharpe
    FROM factor_month
    GROUP BY trial_id
    HAVING stddevSamp(long_short_ret) > 0
),
ladder AS (
    SELECT arrayJoin([1, 2, 5, 10, 25, 50, 100, 160, 240]) AS n
)
SELECT l.n AS factors_tried,
       round(max(s.sharpe), 2) AS best_sharpe,
       round(avg(s.sharpe), 2) AS average_sharpe
FROM ladder AS l
CROSS JOIN scored AS s
WHERE s.trial_id <= l.n
GROUP BY factors_tried
ORDER BY factors_tried

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

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)