Question about the number of patients in the oxygen_delivery table

Prerequisites

• Put an X between the brackets on this line if you have done all of the following:
  • Checked the online documentation: https://mimic.mit.edu/
  • Checked that your issue isn't already addressed: https://github.com/MIT-LCP/mimic-code/issues?utf8=%E2%9C%93&q=

Description

Hello,

We have observed that certain patients exhibit results in the chartevents table with itemid = 226732, but these results are absent in the oxygen_delivery table. One example is stay_id = 30813912.

Upon analyzing line 86 of the oxygen_delivery table, we discovered that placing the condition "where ce.rn = 1" here only includes patients present in the ce_stg2 table. Consequently, data for patients existing in the o2 table but not in the ce_stg2 table is missing.

Our solution is to remove this line of code and create a new table, ce_stg3. Do you have any suggestions for a better solution?

Thank you!

Daonan Chen & Zhou Zhou

, ce_stg2 AS ( select ce.subject_id , ce.stay_id , ce.charttime , itemid , value , valuenum , valueuom -- retain only 1 row per charttime -- prioritizing the last documented value -- primarily used to subselect o2 flows , ROW_NUMBER() OVER (PARTITION BY subject_id, charttime, itemid ORDER BY storetime DESC) as rn FROM ce_stg1 ce ) , ce_stg3 AS ( SELECT * FROM ce_stg2 WHERE rn = 1 )

Start with the oxygen_delivery query at line 86 and trace how ce_stg1, ce_stg2, and the rn filter are used. Compare stay_id 30813912 and itemid 226732 across chartevents, ce_stg2, and the oxygen source data. Done means the query's intended row-selection behavior is clarified and the missing patient records are handled consistently.