1WITH monthly_t10y2y AS (
2  SELECT 
3    DATE_TRUNC('month', observation_date) as month,
4    AVG(t10y2y) as avg_t10y2y,
5    MIN(t10y2y) as min_t10y2y,
6    MAX(t10y2y) as max_t10y2y
7  FROM "@michal.fred_data.t10y2y_csv"
8  WHERE t10y2y IS NOT NULL
9  GROUP BY DATE_TRUNC('month', observation_date)
10),
11monthly_baa10y AS (
12  SELECT 
13    DATE_TRUNC('month', observation_date) as month,
14    AVG(baa10y) as avg_baa10y,
15    MIN(baa10y) as min_baa10y,
16    MAX(baa10y) as max_baa10y
17  FROM "@michal.fred_data.baa10y_csv"
18  WHERE baa10y IS NOT NULL
19  GROUP BY DATE_TRUNC('month', observation_date)
20),
21monthly_ic4wsa AS (
22  SELECT 
23    DATE_TRUNC('month', observation_date) as month,
24    AVG(ic4wsa) as avg_ic4wsa,
25    MIN(ic4wsa) as min_ic4wsa,
26    MAX(ic4wsa) as max_ic4wsa
27  FROM "@michal.fred_data.ic4wsa_csv"
28  WHERE ic4wsa IS NOT NULL
29  GROUP BY DATE_TRUNC('month', observation_date)
30),
31monthly_vixcls AS (
32  SELECT 
33    DATE_TRUNC('month', observation_date) as month,
34    AVG(vixcls) as avg_vixcls,
35    MIN(vixcls) as min_vixcls,
36    MAX(vixcls) as max_vixcls
37  FROM "@michal.fred_data.vixcls_csv"
38  WHERE vixcls IS NOT NULL
39  GROUP BY DATE_TRUNC('month', observation_date)
40),
41monthly_umcsent AS (
42  SELECT 
43    DATE_TRUNC('month', observation_date) as month,
44    AVG(umcsent) as avg_umcsent
45  FROM "@michal.fred_data.umcsent_csv"
46  WHERE umcsent IS NOT NULL
47  GROUP BY DATE_TRUNC('month', observation_date)
48),
49monthly_usslind AS (
50  SELECT 
51    DATE_TRUNC('month', observation_date) as month,
52    AVG(usslind) as avg_usslind
53  FROM "@michal.fred_data.usslind_csv"
54  WHERE usslind IS NOT NULL
55  GROUP BY DATE_TRUNC('month', observation_date)
56),
57monthly_unrate AS (
58  SELECT 
59    DATE_TRUNC('month', observation_date) as month,
60    AVG(unrate) as avg_unrate
61  FROM "@michal.fred_data.unrate_csv"
62  WHERE unrate IS NOT NULL
63  GROUP BY DATE_TRUNC('month', observation_date)
64),
65all_months AS (
66  SELECT DISTINCT month FROM monthly_t10y2y
67  UNION
68  SELECT DISTINCT month FROM monthly_baa10y
69  UNION
70  SELECT DISTINCT month FROM monthly_ic4wsa
71  UNION
72  SELECT DISTINCT month FROM monthly_vixcls
73  UNION
74  SELECT DISTINCT month FROM monthly_umcsent
75  UNION
76  SELECT DISTINCT month FROM monthly_usslind
77  UNION
78  SELECT DISTINCT month FROM monthly_unrate
79)
80
81SELECT 
82  am.month,
83  ROUND(t.avg_t10y2y, 2) as yield_spread_10y2y,
84  ROUND(b.avg_baa10y, 2) as corp_bond_spread,
85  ROUND(i.avg_ic4wsa, 0) as initial_claims_4wk,
86  ROUND(v.avg_vixcls, 2) as vix_volatility,
87  ROUND(u.avg_umcsent, 1) as consumer_sentiment,
88  ROUND(l.avg_usslind, 2) as leading_index,
89  ROUND(un.avg_unrate, 1) as unemployment_rate
90FROM all_months am
91LEFT JOIN monthly_t10y2y t ON am.month = t.month
92LEFT JOIN monthly_baa10y b ON am.month = b.month
93LEFT JOIN monthly_ic4wsa i ON am.month = i.month
94LEFT JOIN monthly_vixcls v ON am.month = v.month
95LEFT JOIN monthly_umcsent u ON am.month = u.month
96LEFT JOIN monthly_usslind l ON am.month = l.month
97LEFT JOIN monthly_unrate un ON am.month = un.month
98WHERE am.month >= '2022-01-01'
99ORDER BY am.month asc
100
101