Table medicare.enrollments

Overview for medicare.enrollments

Cleaned up and unified Medicare beneficiaries enrollments table, by state and year. This table is indexed and optimized for efficient queries

Child table of medicare.beneficiaries

Transformed from medicare._enrollments

Primary Key: bene_id, year, state

SQL/DDL Statement

CREATE table medicare.enrollments AS SELECT * 
FROM medicare._enrollments
;
ALTER table medicare.enrollments ADD COLUMN hmo BOOLEAN GENERATED ALWAYS AS (CASE WHEN hmo_cvg_count IS NULL THEN NULL WHEN hmo_cvg_count > 0 THEN TRUE ELSE FALSE END) STORED;
ALTER table medicare.enrollments ADD COLUMN buyin BOOLEAN GENERATED ALWAYS AS (CASE WHEN buyin_cvg_count IS NULL THEN NULL WHEN buyin_cvg_count > 0 THEN TRUE ELSE FALSE END) STORED;
ALTER table medicare.enrollments ADD COLUMN dual BOOLEAN GENERATED ALWAYS AS (CASE WHEN dual_cvg_count IS NULL THEN NULL WHEN dual_cvg_count > 0 THEN TRUE ELSE FALSE END) STORED;
ALTER table medicare.enrollments ADD COLUMN bene HLL_HASHVAL GENERATED ALWAYS AS (hll_hash_text(bene_id)) STORED;
ALTER table medicare.enrollments ADD PRIMARY KEY (bene_id, year, state);
ALTER table medicare.enrollments ADD CONSTRAINT enrollments_to_beneficiaries FOREIGN KEY (bene_id) REFERENCES medicare.beneficiaries (bene_id);

COMMENT ON table medicare.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.768407';

---

Columns:

Column Name	Column Type	Datatype
age	copied	string
age_min	copied	string
bene	generated	HLL_HASHVAL
bene_id	copied	string
buyin	generated	boolean
buyin_cvg_count	copied	string
buyin_indicators	copied	string
curec	copied	string
died	copied	string
dual	generated	boolean
dual_cvg_count	copied	string
dual_indicators	copied	string
file	copied	string
fips2	copied	string
fips3	copied	string
fips3_is_approximated	copied	string
fips3_valdiated	copied	string
fips5	copied	string
hmo	generated	boolean
hmo_cvg_count	copied	string
hmo_indicators	copied	string
orec	copied	string
record	copied	string
residence_counties	copied	string
residence_county	copied	string
ssa2	copied	string
ssa2_list	copied	string
ssa3	copied	string
ssa3_list	copied	string
state	copied	string
state_count	copied	string
state_iso	copied	char(5)
year	copied	string
zip	copied	string
zips	copied	string