It's for picking Treasuries to invest in based on when they mature.
It makes a long list of bins for tracking the maturity date. The bins get steadily larger the farther out because some of the Treasuries are only issued once a quarter.
WITH total_sum AS (
SELECT
GREATEST(ROUND((SELECT SUM(value) FROM ustreasury.treasurydirect_bonds WHERE maturity_date > NOW()::DATE) / 1000), 25)::INT AS temp_forward_count
), full_series AS (
SELECT
GENERATE_SERIES(
0, 29, 1
) AS year_series
UNION ALL
SELECT
GENERATE_SERIES(
30, 59, 2
)
UNION ALL
SELECT
GENERATE_SERIES(
60, 89, 3
)
UNION ALL
SELECT
GENERATE_SERIES(
90, 119, 4
)
UNION ALL
SELECT
GENERATE_SERIES(
120, 149, 5
)
UNION ALL
SELECT
GENERATE_SERIES(
150, 179, 6
)
UNION ALL
SELECT
GENERATE_SERIES(
180, 209, 7
)
UNION ALL
SELECT
GENERATE_SERIES(
210, 239, 8
)
UNION ALL
SELECT
GENERATE_SERIES(
240, 269, 9
)
UNION ALL
SELECT
GENERATE_SERIES(
270, 299, 10
)
UNION ALL
SELECT
GENERATE_SERIES(
300, 329, 15
)
UNION ALL
SELECT
GENERATE_SERIES(
330, 360, 20
)
), current_dates AS (
SELECT
*,
EXTRACT('month' FROM NOW()) AS cur_month,
EXTRACT('year' FROM NOW()) AS cur_year
FROM full_series
), month_blocks AS (
SELECT
*,
((year_series + cur_month) % 12) + 1 AS start_month,
cur_year + (CEIL(((year_series + cur_month) + 1) / 12) - 1) AS start_year,
COALESCE(
(
(LAG(year_series, -1) OVER (ORDER BY year_series) + cur_month)
% 12
) + 1,
cur_month
) AS end_month,
COALESCE(cur_year + (
CEIL(
(
(LAG(year_series, -1) OVER (ORDER BY year_series) + cur_month)
+ 1)
/ 12) - 1
),
cur_year + 30
) AS end_year
FROM current_dates
), date_blocks AS (
SELECT
to_date(CONCAT(start_month, '/', start_year), 'MM/YYYY') AS start_date,
DATE_SUBTRACT(to_date(CONCAT(end_month, '/', end_year), 'MM/YYYY'), '1 day')::DATE AS end_date
FROM month_blocks
), weeks_averages AS (
SELECT
*,
ROUND((start_date - NOW()::DATE) / 7.0) AS start_diff,
ROUND((end_date - NOW()::DATE) / 7.0) AS end_diff,
ROUND((((start_date - NOW()::DATE) / 7.0) + ((end_date - NOW()::DATE) / 7.0)) / 2.0) bin_average
FROM date_blocks
), nearest_lengths AS (
SELECT
*,
CASE LEAST(ABS(bin_average - 4),
ABS(bin_average - 6),
ABS(bin_average - 8),
ABS(bin_average - 13),
ABS(bin_average - 17),
ABS(bin_average - 26),
ABS(bin_average - 52),
ABS(bin_average - 104),
ABS(bin_average - 156),
ABS(bin_average - 260),
ABS(bin_average - 364),
ABS(bin_average - 520),
ABS(bin_average - 1040),
ABS(bin_average - 1560))
WHEN ABS(bin_average - 4) THEN 4
WHEN ABS(bin_average - 6) THEN 6
WHEN ABS(bin_average - 8) THEN 8
WHEN ABS(bin_average - 13) THEN 13
WHEN ABS(bin_average - 17) THEN 17
WHEN ABS(bin_average - 26) THEN 26
WHEN ABS(bin_average - 52) THEN 52
WHEN ABS(bin_average - 104) THEN 104
WHEN ABS(bin_average - 156) THEN 156
WHEN ABS(bin_average - 260) THEN 260
WHEN ABS(bin_average - 364) THEN 364
WHEN ABS(bin_average - 520) THEN 520
WHEN ABS(bin_average - 1040) THEN 1040
WHEN ABS(bin_average - 1560) THEN 1560
ELSE 0
END AS closest_term
FROM weeks_averages
), add_mats_money AS (
SELECT
*,
ROUND(closest_term / 52.0) AS closest_years,
ROUND(COALESCE((SELECT SUM(value) FROM ustreasury.treasurydirect_bonds WHERE maturity_date >= start_date AND maturity_date <= end_date), 0), 2)AS maturing_sum
FROM nearest_lengths
LIMIT (SELECT temp_forward_count FROM total_sum)
) SELECT
*,
ROUND(AVG(maturing_sum) OVER (ORDER BY start_date ROWS BETWEEN 6 PRECEDING AND 6 FOLLOWING), 3) rolling_avg
FROM add_mats_money
ORDER BY rolling_avg, maturing_sum, start_date