Materialized view medicare.qc_enrollments

Overview for medicare.qc_enrollments

Aggregate data for beneficiaries and enrollments

Transformed from medicare.qc_enrl_bene

SQL/DDL Statement

CREATE materialized view  medicare.qc_enrollments AS
SELECT
    year,
	state,
	zip,
	fips3,
	orec,
	curec,
	hmo,
	dual,
	buyin,
	consistent_dob,
	consistent_dod,
	consistent_sex,
	consistent_race,
	fips3_is_approximated,
	fips3_valdiated,
	MAX(state_iso) AS state_iso,
	COUNT(*) AS NumRecords,
	(#(hll_add_agg(bene)))::BIGINT AS NumDistinctBeneficaries,
	hll_add_agg(bene) AS bene_hll
FROM medicare.qc_enrl_bene

GROUP BY year,state,zip,fips3,orec,curec,hmo,dual,buyin,consistent_dob,consistent_dod,consistent_sex,consistent_race,fips3_is_approximated,fips3_valdiated;

COMMENT ON materialized view medicare.qc_enrollments IS 'CREATED BY Dorieh: {"version": "0.2.3", "url": "https://github.com/NSAPH-Data-Platform/dorieh", "commit": "bf4644403297aaf478f6a42717508dde55da409a"}. Created at 2024-08-12 13:31:09.791842';

---

Columns:

Column Name	Column Type	Datatype
bene_hll	computed	string
buyin		string
consistent_dob		string
consistent_dod		string
consistent_race		string
consistent_sex		string
curec		string
dual		string
fips3		string
fips3_is_approximated		string
fips3_valdiated		string
hmo		string
numdistinctbeneficaries	computed	BIGINT
numrecords	computed	BIGINT
orec		string
state		string
state_iso	computed	CHAR(5)
year		string
zip		string