1WITH
2  manufacturing AS (
3    SELECT
4      DATE_TRUNC('year', date::DATE) AS period,
5      AVG(value_raw) AS manufacturing_index
6    FROM
7      "@fred.production_business_activity.observations"
8    WHERE
9      series_id = 'IPB00004SQ' -- Manufacturing
10      AND date >= '2016-01-01'
11    GROUP BY
12      DATE_TRUNC('year', date::DATE)
13  ),
14  mining AS (
15    SELECT
16      DATE_TRUNC('year', date::DATE) AS period,
17      AVG(value_raw) AS mining_index
18    FROM
19      "@fred.production_business_activity.observations"
20    WHERE
21      series_id = 'IPMINE' -- Mining
22      AND date >= '2016-01-01'
23    GROUP BY
24      DATE_TRUNC('year', date::DATE)
25  ),
26  utilities AS (
27    SELECT
28      DATE_TRUNC('year', date::DATE) AS period,
29      AVG(value_raw) AS utilities_index
30    FROM
31      "@fred.production_business_activity.observations"
32    WHERE
33      series_id = 'IPUTIL' -- Utilities
34      AND date >= '2016-01-01'
35    GROUP BY
36      DATE_TRUNC('year', date::DATE)
37  ),
38  sp500 AS (
39    SELECT
40      DATE_TRUNC('year', date::DATE) AS period,
41      AVG(value_raw) AS sp500_index
42    FROM
43      "@fred.money_banking_finance.observations"
44    WHERE
45      series_id = 'SP500'
46      AND date >= '2016-01-01'
47    GROUP BY
48      DATE_TRUNC('year', date::DATE)
49  )
50SELECT
51  COALESCE(m.period, mi.period, u.period, s.period) AS date,
52  ROUND(m.manufacturing_index, 1) AS manufacturing_index,
53  ROUND(mi.mining_index, 1) AS mining_index,
54  ROUND(u.utilities_index, 1) AS utilities_index,
55  ROUND(s.sp500_index, 0) AS sp500_index
56FROM
57  manufacturing m
58  FULL OUTER JOIN mining mi ON m.period = mi.period
59  FULL OUTER JOIN utilities u ON m.period = u.period
60  FULL OUTER JOIN sp500 s ON m.period = s.period
61ORDER BY
62  date ASC