text: with t1 as
  (
    select
      co.icustay_id
    , DATETIME_DIFF(mv.starttime, co.intime, SECOND)/60.0/60.0 as hr
    -- standardize the units to millilitres
    -- also metavision has floating point precision.. but we only care down to the mL
    , round(case
        when mv.amountuom = 'L'
          then mv.amount * 1000.0
        when mv.amountuom = 'ml'
          then mv.amount
      else null end) as amount
    from `physionet-data.mimiciii_demo`.`icustays` co
    inner join `physionet-data.mimiciii_demo`.`inputevents_mv` mv
    on co.icustay_id = mv.icustay_id
    and mv.itemid in
    (
      -- 225943 Solution
      225158, -- NaCl 0.9%
      225828, -- LR
      225944, -- Sterile Water
      225797  -- Free Water
    )
    where mv.statusdescription != 'Rewritten'
    and
    -- in MetaVision, these ITEMIDs appear with a null rate IFF endtime=starttime + 1 minute
    -- so it is sufficient to:
    --    (1) check the rate is > 240 if it exists or
    --    (2) ensure the rate is null and amount > 240 ml
      (
        (mv.rate is not null and mv.rateuom = 'mL/hour' and mv.rate > 248)
        OR (mv.rate is not null and mv.rateuom = 'mL/min' and mv.rate > (248/60.0))
        OR (mv.rate is null and mv.amountuom = 'L' and mv.amount > 0.248)
        OR (mv.rate is null and mv.amountuom = 'ml' and mv.amount > 248)
      )
  )
  , t2 as
  (
    select
      co.icustay_id
    , DATETIME_DIFF(cv.charttime, co.intime, SECOND)/60.0/60.0 as hr
    -- carevue always has units in millilitres
    , round(cv.amount) as amount
    from `physionet-data.mimiciii_demo`.`icustays` co
    inner join `physionet-data.mimiciii_demo`.`inputevents_cv` cv
    on co.icustay_id = cv.icustay_id
    and cv.itemid in
    (
      30018 --	.9% Normal Saline
    , 30021 --	Lactated Ringers
    , 30058 --	Free Water Bolus
    , 40850 --	ns bolus
    , 41491 --	fluid bolus
    , 42639 --	bolus
    , 30065 --	Sterile Water
    , 42187 --	free h20
    , 43819 --	1:1 NS Repletion.
    , 30063 --	IV Piggyback
    , 41430 --	free water boluses
    , 40712 --	free H20
    , 44160 --	BOLUS
    , 42383 --	cc for cc replace
    , 30169 --	Sterile H20_GU
    , 42297 --	Fluid bolus
    , 42453 --	Fluid Bolus
    , 40872 --	free water
    , 41915 --	FREE WATER
    , 41490 --	NS bolus
    , 46501 --	H2O Bolus
    , 45045 --	WaterBolus
    , 41984 --	FREE H20
    , 41371 --	ns fluid bolus
    , 41582 --	free h20 bolus
    , 41322 --	rl bolus
    , 40778 --	Free H2O
    , 41896 --	ivf boluses
    , 41428 --	ns .9% bolus
    , 43936 --	FREE WATER BOLUSES
    , 44200 --	FLUID BOLUS
    , 41619 --	frfee water boluses
    , 40424 --	free H2O
    , 41457 --	Free H20 intake
    , 41581 --	Water bolus
    , 42844 --	NS fluid bolus
    , 42429 --	Free water
    , 41356 --	IV Bolus
    , 40532 --	FREE H2O
    , 42548 --	NS Bolus
    , 44184 --	LR Bolus
    , 44521 --	LR bolus
    , 44741 --	NS FLUID BOLUS
    , 44126 --	fl bolus
    , 44110 --	RL BOLUS
    , 44633 --	ns boluses
    , 44983 --	Bolus NS
    , 44815 --	LR BOLUS
    , 43986 --	iv bolus
    , 45079 --	500 cc ns bolus
    , 46781 --	lr bolus
    , 45155 --	ns cc/cc replacement
    , 43909 --	H20 BOlus
    , 41467 --	NS IV bolus
    , 44367 --	LR
    , 41743 --	water bolus
    , 40423 --	Bolus
    , 44263 --	fluid bolus ns
    , 42749 --	fluid bolus NS
    , 45480 --	500cc ns bolus
    , 44491 --	.9NS bolus
    , 41695 --	NS fluid boluses
    , 46169 --	free water bolus.
    , 41580 --	free h2o bolus
    , 41392 --	ns b
    , 45989 --	NS Fluid Bolus
    , 45137 --	NS cc/cc
    , 45154 --	Free H20 bolus
    , 44053 --	normal saline bolus
    , 41416 --	free h2o boluses
    , 44761 --	Free H20
    , 41237 --	ns fluid boluses
    , 44426 --	bolus ns
    , 43975 --	FREE H20 BOLUSES
    , 44894 --	N/s 500 ml bolus
    , 41380 --	nsbolus
    , 42671 --	free h2o
    )
    where cv.amount > 248
    and cv.amount < 2000
    and cv.amountuom = 'ml'
  )
  select
      icustay_id
    , hr
    , sum(amount) as crystalloid_bolus
  from t1
  -- just because the rate was high enough, does *not* mean the final amount was
  where amount > 248
  group by t1.icustay_id, t1.hr
  UNION ALL
  select
      icustay_id
    , hr
    , sum(amount) as crystalloid_bolus
  from t2
  group by t2.icustay_id, t2.hr
  order by icustay_id, hr;