Búsqueda de SQL (Postgres) para calcular el Covímetro a partir de mi base de datos de COVID-19 en Puerto Rico.

Covímetro 2020-09-27.csv

covímetro.sql

WITH terms AS (
	SELECT
		bulletin_date,
		datum_date,
		sum(confirmed_cases) OVER older AS old_sum,
		lag(confirmed_cases, 3) OVER current AS lag3,
		lag(confirmed_cases, 2) OVER current AS lag2,
		lag(confirmed_cases, 1) OVER current AS lag1,
		confirmed_cases,
		lead(confirmed_cases, 1) OVER current AS lead1,
		lead(confirmed_cases, 2) OVER current AS lead2,
		lead(confirmed_cases, 3) OVER current AS lead3
	FROM bitemporal
	WINDOW older AS (
		PARTITION BY bulletin_date
		ORDER BY datum_date
		RANGE BETWEEN '58 day' PRECEDING
			      AND '4 day' PRECEDING
	), current AS (
		PARTITION BY bulletin_date
		ORDER BY datum_date
		RANGE BETWEEN '3 day' PRECEDING
				  AND '3 day' FOLLOWING
	)
), sums AS (
SELECT
	bulletin_date,
	datum_date,
	confirmed_cases,
	(7 * (old_sum + lag3)
		+ 6 * lag2
		+ 5 * lag1
		+ 4 * confirmed_cases
		+ 3 * lead1
		+ 2 * lead2
		+ lead3) / 7.0
		AS sum_a,
	(7 * old_sum
			+ 6 * lag3
			+ 5 * lag2
			+ 4 * lag1
			+ 3 * confirmed_cases
			+ 2 * lead1
			+ lead2) / 7.0
			AS sum_b
	FROM terms
)
SELECT
	bulletin_date,
	datum_date,
	confirmed_cases,
	sum_a,
	sum_b,
	(sum_a - sum_b) / sum_b
		AS covimetro
FROM sums
ORDER BY bulletin_date DESC, datum_date DESC;