Difference in returned record when running the same query to obtain aki_stage_creat in MySQL and BigQuery (edit to previously closed issue)

[rielmvp]

Prerequisites

• [ X] 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

I'm working on a project to apply machine learning techniques to predict AKI using the KDIGO serum creatinine and urine output criteria. I noticed the MIMIC code repository has SQL scripts ready to create derived tables and query results such as 'kdigo_creatinine'. I'm working on two SQL environments now, BigQuery on the cloud and MySQL on a server. I wanted to validate if the results I got in MySQL (since there are no MySQL scripts for the MIMIC code concepts) are correct, so I ran a query to get KDIGO_creatinine as follows:

SELECT aki_stage_creat, count(aki_stage_creat)
FROM (
with cr as
(
select
ie.icustay_id
, ie.intime, ie.outtime
, le.valuenum as creat
, le.charttime
, ie.DBSOURCE
from ICUSTAYS ie
left join LABEVENTS le
on ie.subject_id = le.subject_id
and le.ITEMID = 50912 #Creatinine (not Creatinine, Serum)
and le.VALUENUM is not null
and le.CHARTTIME between DATE_SUB(ie.intime, interval '7' day) and DATE_ADD(ie.intime, interval '7' day)
)
-- add in the lowest value in the previous 48 hours/7 days
SELECT
cr.icustay_id
, cr.charttime
, cr.creat
, MIN(cr48.creat) AS creat_low_past_48hr
, MIN(cr7.creat) AS creat_low_past_7day
-- added case to count the stage of AKI by creatinine
, CASE
-- 3x baseline
WHEN cr.creat >= (MIN(cr7.creat)3.0) then 3
-- OR cr >= 4.0 with associated increase
WHEN cr.creat >= 4
-- For patients reaching Stage 3 by SCr >4.0 mg/dl
-- require that the patient first achieve ... acute increase >= 0.3 within 48 hr
-- or an increase of >= 1.5 times baseline
and (MIN(cr48.creat) <= 3.7 OR cr.creat >= (1.5MIN(cr7.creat)))
then 3
-- TODO: initiation of RRT
when cr.creat >= (MIN(cr7.creat)*2.0) then 2
when cr.creat >= (MIN(cr48.creat)+0.3) then 1
when cr.creat >= (MIN(cr7.creat)*1.5) then 1
else 0 end as aki_stage_creat
FROM cr
-- add in all creatinine values in the last 48 hours
LEFT JOIN cr cr48
ON cr.icustay_id = cr48.icustay_id
AND cr48.charttime < cr.charttime
AND cr48.charttime >= DATE_SUB(cr.charttime, INTERVAL '48' HOUR)
-- add in all creatinine values in the last 7 days
LEFT JOIN cr cr7
ON cr.icustay_id = cr7.icustay_id
AND cr7.charttime < cr.charttime
AND cr7.charttime >= DATE_SUB(cr.charttime, INTERVAL '7' DAY)
GROUP BY cr.icustay_id, cr.charttime, cr.creat
ORDER BY cr.icustay_id, cr.charttime, cr.creat) creat
GROUP BY aki_stage_creat;

I understand that it's not exactly the same SQL query as the one in MIMIC code to create the kdigo_creatinine derived table. I just wanted to verify if the results I got were the same in MySQL and BigQuery, and it seems that I got different results. I don't think it's a MIMIC version issue since the MySQL implementation is v1.4 and I run the checks.sql script to find out that all the checks passed (all the row counts of the MySQL). Here are pictures to show difference in results when running the same script:

BigQuery

MySQL

I want to work on this project using MySQL due to its convenience in interacting with Python, but I'm worried because the MIMIC code concepts are not provided in MySQL and I might make mistakes from conversion of available PostgreSQL and BigQuery scripts to MySQL. I think, even between PostgreSQL and BigQuery there are SQL conversion problems (as identified in issue #1549). Could there be some underlying explanation behind this difference? Also, is there any proposed way to replicate the MIMIC code concepts, especially regarding KDIGO AKI patient detection using MySQL?

Thanks beforehand,