 select *, 
 row_number() over(partition by caseid order by primaryid desc, database desc, fda_dt desc, i_f_code, isr desc) as row_num 
 from all_casedemo 
-) a where a.row_num = 1
+) a where a.row_num = 1;
 
 -- remove any duplicates based on fully populated matching demographic key fields and exact match on list of drugs and list of outcomes (FAERS reactions)
 -- NOTE. when using this table for subsequent joins in the ETL process, join to FAERS data using primaryid and join to LAERS data using isr

create table casedemo_legacy as
select "CASE", i_f_cod, event_dt, age, gndr_cod, reporter_country, d.isr, drugname_list, reac_pt_list, fda_dt
from demo_legacy d
inner join drugname_legacy_list dl
on d.isr = dl.isr 
inner join reac_pt_legacy_list rpl
on d.isr = rpl.isr;

------------------------------

select caseid, event_dt, sex, reporter_country, max(age) as default_age
from all_casedemo 
where caseid is not null and event_dt is not null and age is not null and sex is not null and reporter_country is not null
group by caseid, event_dt, sex, reporter_country;

-- single imputation of missing age 
update all_casedemo a 
