 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 as
select caseid, caseversion, i_f_code, event_dt, age, sex, reporter_country, d.primaryid, drugname_list, reac_pt_list, fda_dt
from demo d
inner join drugname_list dl
on d.primaryid = dl.primaryid 
inner join reac_pt_list rpl
on d.primaryid = rpl.primaryid;

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

select caseid, age, sex, reporter_country, max(event_dt) as default_event_dt
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, age, sex, reporter_country;

-- single imputation of missing event_dt 
update all_casedemo a
