View medicare._beneficiaries

Overview for medicare._beneficiaries

internal intermediate view adding some data conversions

Aggregated from medicare._ps on On bene_id

Primary Key: bene_id

SQL/DDL Statement

CREATE view  medicare._beneficiaries AS
SELECT
    bene_id,
	MIN(dob) AS dob,
	MIN(yob) AS yob,
	MAX(dod) AS dod,
	MIN(year) AS first_enrollment_year,
	MAX(year) AS last_enrollment_year,
	ARRAY_AGG(DISTINCT year ORDER BY year) AS all_enrollment_years,
	string_agg(distinct race, ',') AS race,
	string_agg(distinct race_rti, ',') AS race_rti,
	string_agg(distinct sex, ',') AS sex,
	(array_agg(orec ORDER BY year, orec))[1] AS orec,
	CASE
		  WHEN MIN(orec) <> MAX(orec) THEN MAX(orec)
		END AS orec_latest,
	(
		  COUNT(distinct (dob, race, sex)) - 1 +
		  CASE
		    (COUNT(DISTINCT dod) FILTER (WHERE dod is not null))
		    WHEN 0 THEN 0
		    ELSE COUNT(DISTINCT dod) FILTER (WHERE dod is not null) - 1
		  END
		) AS discrepancies,
	CASE
		  WHEN MAX(dob) <> MIN(dob) THEN  MAX(dob)
		END AS dob_latest,
	CASE
		  WHEN MAX(yob) <> MIN(yob) THEN  MAX(yob)
		END AS yob_latest,
	CASE
		  WHEN MIN(dod) <> MAX(dod) THEN MIN(dod)
		END AS dod_earliest
FROM medicare._ps

WHERE bene_id IS NOT NULL
GROUP BY bene_id;

COMMENT ON view medicare._beneficiaries IS 'CREATED BY Dorieh: {"version": "0.4.5", "url": "file:///Users/misha/harvard/github/dorieh", "commit": null}. Created at 2026-07-13 11:20:49.401933';

---

Columns:

Column Name	Column Type	Datatype
all_enrollment_years	computed	INT[]
bene_id	grouping	string
discrepancies	computed	string
dob	computed	string
dob_latest	computed	string
dod	computed	string
dod_earliest	computed	string
first_enrollment_year	computed	string
last_enrollment_year	computed	string
orec	computed	string
orec_latest	computed	string
race	computed	string
race_rti	computed	string
sex	computed	string
yob	computed	string
yob_latest	computed	string