Neotree db explore

neotree-deduplicate.sql

-- First we create a view where we deduplicate the sessions
create materialized view deduplicated_sessions as 
(
  with latest_sessions as (
    select
      scriptid,
      uid, 
      max(id) as id -- This takes the most recent upload 
                    -- of the session as the deduplicated record. 
                    -- We could replace with min(id) to take the 
                    -- first record uploaded
    from sessions
    group by 1,2
  )
  select
    latest_sessions.scriptid,
    latest_sessions.uid,
    latest_sessions.id,
    sessions.ingested_at,
    data
  from latest_sessions join sessions
  on latest_sessions.id = sessions.id
);

-- Now create a derived view of just the admissions...
create materialized view deduplicated_admissions as 
select * from deduplicated_sessions
where scriptid = '-KO1TK4zMvLhxTw6eKia';

-- ...and the discharges
create materialized view deduplciated_discharges as 
select * from deduplicated_sessions
where scriptid = '-KYDiO2BTM4kSGZDVXAO';

-- finally we join them together
create materialized view joint_records as
select 
  a.uid,
  a."data" as admission_data,
  d."data" as discharge_data
from deduplicated_admissions a
left join deduplciated_discharges d 
on a.uid = d.uid;