1-- Sector Performance: Construction Spending vs S&P 500
2WITH
3  construction_spending AS (
4    SELECT
5      DATE_TRUNC('month', date::DATE) AS period,
6      AVG(value_raw) AS construction_spending_millions_usd
7    FROM
8      "@fred.production_business_activity.observations"
9    WHERE
10      series_id = 'TTLCONS' -- Total Construction Spending
11      AND date >= '2016-01-01'
12    GROUP BY
13      DATE_TRUNC('month', date::DATE)
14  ),
15  sp500 AS (
16    SELECT
17      DATE_TRUNC('month', date::DATE) AS period,
18      AVG(value_raw) AS sp500_index
19    FROM
20      "@fred.money_banking_finance.observations"
21    WHERE
22      series_id = 'SP500'
23      AND date >= '2016-01-01'
24    GROUP BY
25      DATE_TRUNC('month', date::DATE)
26  )
27SELECT
28  COALESCE(c.period, s.period) AS date,
29  ROUND(c.construction_spending_millions_usd, 0) AS construction_spending_millions_usd,
30  ROUND(s.sp500_index, 0) AS sp500_index
31FROM
32  construction_spending c
33  FULL OUTER JOIN sp500 s ON c.period = s.period
34ORDER BY
35  date ASC