1WITH monthly_values AS (
2  SELECT 
3    DATE_TRUNC('month', observation_date) as month,
4    MAX(umcsent) as consumer_sentiment
5  FROM "@michal.fred_data.umcsent_csv"
6  WHERE observation_date >= '2020-01-01'
7  GROUP BY DATE_TRUNC('month', observation_date)
8),
9monthly_unemployment AS (
10  SELECT 
11    DATE_TRUNC('month', observation_date) as month,
12    MAX(unrate) as unemployment_rate
13  FROM "@michal.fred_data.unrate_csv"
14  WHERE observation_date >= '2020-01-01'
15  GROUP BY DATE_TRUNC('month', observation_date)
16),
17daily_indicators AS (
18  SELECT 
19    t.observation_date as date,
20    t.t10y2y,
21    b.baa10y,
22    v.vixcls,
23    mv.consumer_sentiment,
24    mu.unemployment_rate
25  FROM "@michal.fred_data.t10y2y_csv" t
26  LEFT JOIN "@michal.fred_data.baa10y_csv" b ON t.observation_date = b.observation_date
27  LEFT JOIN "@michal.fred_data.vixcls_csv" v ON t.observation_date = v.observation_date
28  LEFT JOIN monthly_values mv ON DATE_TRUNC('month', t.observation_date) = mv.month
29  LEFT JOIN monthly_unemployment mu ON DATE_TRUNC('month', t.observation_date) = mu.month
30  WHERE t.observation_date >= '2020-01-01'
31),
32indicator_ranges AS (
33  SELECT 
34    MIN(t10y2y) as min_yield_spread, MAX(t10y2y) as max_yield_spread,
35    MIN(baa10y) as min_corp_spread, MAX(baa10y) as max_corp_spread,
36    MIN(vixcls) as min_vix, MAX(vixcls) as max_vix,
37    MIN(consumer_sentiment) as min_sentiment, MAX(consumer_sentiment) as max_sentiment,
38    MIN(unemployment_rate) as min_unrate, MAX(unemployment_rate) as max_unrate
39  FROM daily_indicators
40),
41normalized_daily AS (
42  SELECT 
43    di.date,
44    di.t10y2y,
45    di.baa10y,
46    di.vixcls,
47    di.consumer_sentiment,
48    di.unemployment_rate,
49    CASE 
50      WHEN di.t10y2y IS NOT NULL AND ir.max_yield_spread != ir.min_yield_spread 
51      THEN ((di.t10y2y - ir.min_yield_spread) / (ir.max_yield_spread - ir.min_yield_spread)) * 100
52      ELSE NULL
53    END as yield_spread_score,
54    CASE 
55      WHEN di.baa10y IS NOT NULL AND ir.max_corp_spread != ir.min_corp_spread
56      THEN (1 - (di.baa10y - ir.min_corp_spread) / (ir.max_corp_spread - ir.min_corp_spread)) * 100
57      ELSE NULL
58    END as corp_spread_score,
59    CASE 
60      WHEN di.vixcls IS NOT NULL AND ir.max_vix != ir.min_vix
61      THEN (1 - (di.vixcls - ir.min_vix) / (ir.max_vix - ir.min_vix)) * 100
62      ELSE NULL
63    END as vix_score,
64    CASE 
65      WHEN di.consumer_sentiment IS NOT NULL AND ir.max_sentiment != ir.min_sentiment
66      THEN ((di.consumer_sentiment - ir.min_sentiment) / (ir.max_sentiment - ir.min_sentiment)) * 100
67      ELSE NULL
68    END as sentiment_score,
69    CASE 
70      WHEN di.unemployment_rate IS NOT NULL AND ir.max_unrate != ir.min_unrate
71      THEN (1 - (di.unemployment_rate - ir.min_unrate) / (ir.max_unrate - ir.min_unrate)) * 100
72      ELSE NULL
73    END as unemployment_score
74  FROM daily_indicators di
75  CROSS JOIN indicator_ranges ir
76  WHERE di.date >= '2025-01-01' AND di.date < '2026-01-01'
77),
78daily_composite AS (
79  SELECT 
80    date,
81    t10y2y,
82    baa10y,
83    vixcls,
84    consumer_sentiment,
85    unemployment_rate,
86    (COALESCE(yield_spread_score, 0) + 
87     COALESCE(corp_spread_score, 0) + 
88     COALESCE(vix_score, 0) + 
89     COALESCE(sentiment_score, 0) + 
90     COALESCE(unemployment_score, 0)) / 
91    NULLIF((CASE WHEN yield_spread_score IS NOT NULL THEN 1 ELSE 0 END +
92     CASE WHEN corp_spread_score IS NOT NULL THEN 1 ELSE 0 END +
93     CASE WHEN vix_score IS NOT NULL THEN 1 ELSE 0 END +
94     CASE WHEN sentiment_score IS NOT NULL THEN 1 ELSE 0 END +
95     CASE WHEN unemployment_score IS NOT NULL THEN 1 ELSE 0 END), 0) as composite_score,
96    (CASE WHEN yield_spread_score IS NOT NULL THEN 1 ELSE 0 END +
97     CASE WHEN corp_spread_score IS NOT NULL THEN 1 ELSE 0 END +
98     CASE WHEN vix_score IS NOT NULL THEN 1 ELSE 0 END +
99     CASE WHEN sentiment_score IS NOT NULL THEN 1 ELSE 0 END +
100     CASE WHEN unemployment_score IS NOT NULL THEN 1 ELSE 0 END) as indicator_count
101  FROM normalized_daily
102),
103recession_2025_daily AS (
104  SELECT 
105    date,
106    price * 100 as recession_prob_2025
107  FROM "@polymarket.clob.daily_token_prices"
108  WHERE token_id = '104173557214744537570424345347209544585775842950109756851652855913015295701992'
109    AND outcome = 'Yes'
110    AND date >= '2025-01-01' AND date < '2026-01-01'
111)
112
113SELECT 
114  dc.date,
115  ROUND(dc.composite_score, 1) as economic_health_score,
116  ROUND(rd.recession_prob_2025, 1) as recession_prob_2025_pct,
117  ROUND(dc.composite_score - (100 - rd.recession_prob_2025), 1) as market_vs_fundamentals,
118  dc.indicator_count as indicators_available,
119  ROUND(dc.t10y2y, 2) as yield_spread,
120  ROUND(dc.baa10y, 2) as corp_spread,
121  ROUND(dc.vixcls, 2) as vix,
122  ROUND(dc.consumer_sentiment, 1) as consumer_sentiment,
123  ROUND(dc.unemployment_rate, 1) as unemployment_rate
124FROM daily_composite dc
125LEFT JOIN recession_2025_daily rd ON dc.date = rd.date
126WHERE dc.indicator_count >= 3
127ORDER BY dc.date asc
128