Composite Momentum Strategy Backtest

WITH daily AS (
  SELECT sq.symbol, sq.date, sq.close, sq.volume,
    ti.rsi_14, ti.sma_50, ti.volume_ma_20,
    LAG(sq.close, 60) OVER (PARTITION BY sq.symbol ORDER BY sq.date) AS close_60d_ago,
    LAG(sq.close, 20) OVER (PARTITION BY sq.symbol ORDER BY sq.date) AS close_20d_ago,
    LEAD(sq.close, 5)  OVER (PARTITION BY sq.symbol ORDER BY sq.date) AS close_fwd5,
    LEAD(sq.close, 21) OVER (PARTITION BY sq.symbol ORDER BY sq.date) AS close_fwd21
  FROM shibui.stock_quotes sq
  INNER JOIN shibui.technical_indicators ti ON sq.symbol = ti.symbol AND sq.date = ti.date
  WHERE sq.date >= DATE '2017-09-01' AND sq.date <= CURRENT_DATE - INTERVAL '35 days'
),
weekly_flag AS (
  SELECT d.*, ROW_NUMBER() OVER (PARTITION BY d.symbol,
    DATE_TRUNC('week', d.date) ORDER BY d.date DESC) AS week_rn
  FROM daily d
),
weekly AS (SELECT * FROM weekly_flag WHERE week_rn = 1),
weekly_prev AS (
  SELECT w.*,
    LAG(w.sma_50) OVER (PARTITION BY w.symbol ORDER BY w.date) AS prev_week_sma50,
    LAG(w.volume_ma_20, 4) OVER (PARTITION BY w.symbol ORDER BY w.date) AS vol_ma20_4wk_ago
  FROM weekly w
),
val AS (
  SELECT symbol, date, market_cap FROM shibui.valuation WHERE date >= DATE '2017-09-01'
),
spy_daily AS (
  SELECT sq.date, sq.close AS spy_close, ti.sma_50 AS spy_sma50,
    LAG(sq.close, 60) OVER (ORDER BY sq.date) AS spy_60d_ago,
    LAG(ti.sma_50) OVER (ORDER BY sq.date) AS spy_prev_sma50,
    LEAD(sq.close, 5) OVER (ORDER BY sq.date) AS spy_fwd5,
    LEAD(sq.close, 21) OVER (ORDER BY sq.date) AS spy_fwd21
  FROM shibui.stock_quotes sq
  INNER JOIN shibui.technical_indicators ti ON sq.symbol = ti.symbol AND sq.date = ti.date
  WHERE sq.ticker = 'SPY' AND sq.date >= DATE '2017-09-01'
    AND sq.date <= CURRENT_DATE - INTERVAL '35 days'
),
combined AS (
  SELECT wp.symbol, wp.date AS week_date, wp.close, wp.volume, wp.rsi_14,
    wp.sma_50, wp.prev_week_sma50, wp.volume_ma_20, wp.vol_ma20_4wk_ago,
    wp.close_60d_ago, wp.close_20d_ago, wp.close_fwd5, wp.close_fwd21,
    v.market_cap, g.type, g.country_iso, g.gics_sub_industry,
    sd.spy_close, sd.spy_sma50, sd.spy_prev_sma50, sd.spy_60d_ago,
    sd.spy_fwd5, sd.spy_fwd21
  FROM weekly_prev wp
  INNER JOIN val v ON wp.symbol = v.symbol AND wp.date = v.date
  INNER JOIN shibui.general_info g ON wp.symbol = g.symbol
  INNER JOIN spy_daily sd ON wp.date = sd.date
),
eligible AS (
  SELECT c.*,
    (c.close - c.close_60d_ago) / NULLIF(c.close_60d_ago,0) AS ret60,
    (c.close - c.close_20d_ago) / NULLIF(c.close_20d_ago,0) AS ret20,
    ((c.close - c.close_60d_ago)/NULLIF(c.close_60d_ago,0))
      - ((c.spy_close - c.spy_60d_ago)/NULLIF(c.spy_60d_ago,0)) AS rs_vs_spy,
    (c.volume_ma_20 - c.vol_ma20_4wk_ago)
      / NULLIF(c.vol_ma20_4wk_ago,0) AS vol_trend,
    (c.close - c.sma_50) / NULLIF(c.sma_50,0) AS trend_quality
  FROM combined c
  WHERE c.type = 'Common Stock' AND c.country_iso = 'US'
    AND (c.gics_sub_industry NOT ILIKE '%Biotechnology%'
         OR c.gics_sub_industry IS NULL)
    AND c.market_cap > 2e9 AND c.close > 10 AND c.volume_ma_20 > 2e6
    AND c.close > c.sma_50 AND c.sma_50 > c.prev_week_sma50
    AND c.rsi_14 BETWEEN 50 AND 68 AND c.volume > c.volume_ma_20
    AND c.spy_close > c.spy_sma50 AND c.spy_sma50 > c.spy_prev_sma50
),
ranked AS (
  SELECT e.*,
    PERCENT_RANK() OVER (PARTITION BY e.week_date ORDER BY e.ret60) AS pr_ret60,
    PERCENT_RANK() OVER (PARTITION BY e.week_date ORDER BY e.ret20) AS pr_ret20,
    PERCENT_RANK() OVER (PARTITION BY e.week_date ORDER BY e.rs_vs_spy) AS pr_rs,
    PERCENT_RANK() OVER (PARTITION BY e.week_date ORDER BY e.vol_trend) AS pr_voltrend,
    PERCENT_RANK() OVER (PARTITION BY e.week_date ORDER BY e.trend_quality) AS pr_trendq
  FROM eligible e
  WHERE e.ret60 IS NOT NULL AND e.ret20 IS NOT NULL
    AND e.rs_vs_spy IS NOT NULL AND e.vol_trend IS NOT NULL
    AND e.trend_quality IS NOT NULL
),
scored AS (
  SELECT r.*,
    (0.30*r.pr_ret60 + 0.25*r.pr_ret20 + 0.20*r.pr_rs
     + 0.15*r.pr_voltrend + 0.10*r.pr_trendq) AS composite_score
  FROM ranked r
),
topN AS (
  SELECT s.*, RANK() OVER (PARTITION BY s.week_date
    ORDER BY s.composite_score DESC) AS rank_in_week
  FROM scored s
),
top5 AS (SELECT * FROM topN WHERE rank_in_week <= 5),
all_returns AS (
  SELECT 'Top 5 (signal)' AS group_label, 5 AS horizon_days,
    (close_fwd5 - close) / NULLIF(close,0) * 100 AS return_pct FROM top5
  UNION ALL
  SELECT 'Top 5 (signal)', 21,
    (close_fwd21 - close) / NULLIF(close,0) * 100 FROM top5
  UNION ALL
  SELECT 'Eligible pool', 5,
    (close_fwd5 - close) / NULLIF(close,0) * 100 FROM eligible
  UNION ALL
  SELECT 'Eligible pool', 21,
    (close_fwd21 - close) / NULLIF(close,0) * 100 FROM eligible
  UNION ALL
  SELECT DISTINCT 'SPY', 5,
    (spy_fwd5 - spy_close)/NULLIF(spy_close,0)*100 FROM combined
  UNION ALL
  SELECT DISTINCT 'SPY', 21,
    (spy_fwd21 - spy_close)/NULLIF(spy_close,0)*100 FROM combined
)
SELECT group_label, horizon_days,
  COUNT(*) AS signals,
  ROUND(AVG(return_pct),3) AS avg_ret_pct,
  ROUND(STDDEV(return_pct),3) AS stddev_pct,
  ROUND(SUM(CASE WHEN return_pct>0 THEN 1 ELSE 0 END)*100.0
    /NULLIF(COUNT(return_pct),0),1) AS win_rate_pct,
  ROUND(AVG(return_pct)/NULLIF(STDDEV(return_pct),0),3) AS sharpe_like
FROM all_returns
WHERE return_pct IS NOT NULL
GROUP BY group_label, horizon_days
ORDER BY horizon_days, group_label
LIMIT 20