1WITH
2  -- Signal 1: IMF World CPI YoY (latest period)
3  imf_cpi AS (
4    SELECT
5      'World CPI YoY (IMF)' AS signal,
6      o.value AS raw_value,
7      ROUND(o.value, 2) AS display_value,
8      '%' AS unit,
9      o.time_period AS as_of,
10      CASE
11        WHEN o.value >= 5.0 THEN '🔴 Critical'
12        WHEN o.value >= 3.5 THEN '🟡 Elevated'
13        WHEN o.value >= 2.0 THEN '🟠 Watch'
14        ELSE '🟢 Contained'
15      END AS status
16    FROM
17      "@imf.cpi_wca.observations" o
18      JOIN "@imf.cpi_wca.codes_cl_country" c ON o.country = c.code
19    WHERE
20      o.type_of_transformation = 'YOY_PCH_PA_PT'
21      AND c.description = 'World'
22      AND o.time_period = (
23        SELECT
24          MAX(time_period)
25        FROM
26          "@imf.cpi_wca.observations"
27        WHERE
28          type_of_transformation = 'YOY_PCH_PA_PT'
29      )
30  ),
31  -- Signal 2: FRED BAA Credit Spread (latest available day)
32  fred_baa AS (
33    SELECT
34      'BAA Credit Spread (FRED)' AS signal,
35      o.value AS raw_value,
36      ROUND(o.value, 2) AS display_value,
37      '%' AS unit,
38      strftime(o.date, '%Y-%m-%d') AS as_of,
39      CASE
40        WHEN o.value >= 3.0 THEN '🔴 Critical'
41        WHEN o.value >= 2.5 THEN '🟡 Elevated'
42        WHEN o.value >= 2.0 THEN '🟠 Watch'
43        ELSE '🟢 Contained'
44      END AS status
45    FROM
46      "@fred.money_banking_finance.observations" o
47    WHERE
48      o.series_id = 'BAA10Y'
49      AND o.value IS NOT NULL
50    ORDER BY
51      o.date DESC
52    LIMIT
53      1
54  ),
55  -- Signal 3: FRED 10Y-2Y Yield Curve (latest available day)
56  fred_yc AS (
57    SELECT
58      '10Y–2Y Yield Curve (FRED)' AS signal,
59      o.value AS raw_value,
60      ROUND(o.value, 2) AS display_value,
61      '%' AS unit,
62      strftime(o.date, '%Y-%m-%d') AS as_of,
63      CASE
64        WHEN o.value <= -0.5 THEN '🔴 Critical' -- deeply inverted
65        WHEN o.value < 0.0 THEN '🟡 Elevated' -- inverted
66        WHEN o.value < 0.5 THEN '🟠 Watch' -- flat / barely positive
67        ELSE '🟢 Contained' -- normal steepening
68      END AS status
69    FROM
70      "@fred.money_banking_finance.observations" o
71    WHERE
72      o.series_id = 'T10Y2Y'
73      AND o.value IS NOT NULL
74    ORDER BY
75      o.date DESC
76    LIMIT
77      1
78  ),
79  -- Signal 4: FRED 5Y Breakeven Inflation (latest available day)
80  fred_bei AS (
81    SELECT
82      '5Y Breakeven Inflation (FRED)' AS signal,
83      o.value AS raw_value,
84      ROUND(o.value, 2) AS display_value,
85      '%' AS unit,
86      strftime(o.date, '%Y-%m-%d') AS as_of,
87      CASE
88        WHEN o.value >= 3.0 THEN '🔴 Critical'
89        WHEN o.value >= 2.5 THEN '🟡 Elevated'
90        WHEN o.value >= 2.0 THEN '🟠 Watch'
91        ELSE '🟢 Contained'
92      END AS status
93    FROM
94      "@fred.money_banking_finance.observations" o
95    WHERE
96      o.series_id = 'T5YIE'
97      AND o.value IS NOT NULL
98    ORDER BY
99      o.date DESC
100    LIMIT
101      1
102  ),
103  -- Signal 5: EIA US Regular Gasoline (latest weekly)
104  eia_gas AS (
105    SELECT
106      'US Regular Gasoline (EIA)' AS signal,
107      value AS raw_value,
108      ROUND(value, 2) AS display_value,
109      '$/gal' AS unit,
110      strftime(period, '%Y-%m-%d') AS as_of,
111      CASE
112        WHEN value >= 4.0 THEN '🔴 Critical'
113        WHEN value >= 3.5 THEN '🟡 Elevated'
114        WHEN value >= 3.0 THEN '🟠 Watch'
115        ELSE '🟢 Contained'
116      END AS status
117    FROM
118      "@usenergy.petroleum.weekly_retail_fuel_prices"
119    WHERE
120      duoarea = 'NUS'
121      AND product = 'EPMR'
122    ORDER BY
123      period DESC
124    LIMIT
125      1
126  ),
127  -- Signal 6: Polymarket US Recession by EOY 2026 (highest-liquidity recession market)
128  poly_rec AS (
129    SELECT
130      'US Recession by EOY 2026 (Polymarket)' AS signal,
131      ROUND(CAST(lasttradeprice AS DOUBLE) * 100, 1) AS raw_value,
132      ROUND(CAST(lasttradeprice AS DOUBLE) * 100, 1) AS display_value,
133      '% prob' AS unit,
134      CURRENT_DATE::VARCHAR AS as_of,
135      CASE
136        WHEN CAST(lasttradeprice AS DOUBLE) >= 0.60 THEN '🔴 Critical'
137        WHEN CAST(lasttradeprice AS DOUBLE) >= 0.40 THEN '🟡 Elevated'
138        WHEN CAST(lasttradeprice AS DOUBLE) >= 0.25 THEN '🟠 Watch'
139        ELSE '🟢 Contained'
140      END AS status
141    FROM
142      "@polymarket.gamma.markets"
143    WHERE
144      active = TRUE
145      AND closed = FALSE
146      AND LOWER(question) LIKE '%recession%'
147    ORDER BY
148      CAST(liquiditynum AS DOUBLE) DESC
149    LIMIT
150      1
151  )
152  -- Final union: all signals in one table
153SELECT
154  signal,
155  display_value AS value,
156  unit,
157  as_of,
158  status
159FROM
160  imf_cpi
161UNION ALL
162SELECT
163  signal,
164  display_value,
165  unit,
166  as_of,
167  status
168FROM
169  fred_baa
170UNION ALL
171SELECT
172  signal,
173  display_value,
174  unit,
175  as_of,
176  status
177FROM
178  fred_yc
179UNION ALL
180SELECT
181  signal,
182  display_value,
183  unit,
184  as_of,
185  status
186FROM
187  fred_bei
188UNION ALL
189SELECT
190  signal,
191  display_value,
192  unit,
193  as_of,
194  status
195FROM
196  eia_gas
197UNION ALL
198SELECT
199  signal,
200  display_value,
201  unit,
202  as_of,
203  status
204FROM
205  poly_rec