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,
	MAX(orec) AS orec,
	(
		  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.2.3", "url": "https://github.com/NSAPH-Data-Platform/dorieh", "commit": "bf4644403297aaf478f6a42717508dde55da409a"}. Created at 2024-08-12 13:31:09.766505';

---

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
race	computed	string
race_rti	computed	string
sex	computed	string
yob	computed	string
yob_latest	computed	string