COL362/632 - DBMS - Assignment 1: …

COL362/632 - DBMS -
Assignment 1: SQL
Queries

Deadline
All submissions should be made on Moodle
by 3rd Feb 2025, 11:59 PM.

General Instructions
Follow all instructions. Submissions not
following these instructions will not be
evaluated and will be given Zero marks.
:
 1. Kindly ensure that this assignment is
completed independently. Collaboration
with external entities, including
individuals, AI agents, websites,
discussion forums, etc., is strictly
prohibited. You can discuss and post
questions on the piazza to seek
clarification until 1st Feb.
2. You must use PostgreSQL 15.x for this
assignment.
3. There are a total of 35 queries in this
assignment.

4. Marking scheme: Queries carry

different weights based on the perceived
difficulty level of the query. These
weights are not revealed to you
(intentionally). The weights will be
released after the evaluation.
5. We will evaluate your assignment in an
automated fashion, so ensure that your
folder names, file names, and directory
structure strictly follow the instructions.
There is a zip file provided for your
convenience. Make sure to change the
filename.
6. Each SQL query will be graded in a
binary; there will be no partial grading.
7. Note that there will be no deadline
extensions. But you can use your 72 late
hours. Refer to lecture notes on course
:
 hours. Refer to lecture notes on course
organization for late-hour rules.
8. Download the cleaned-up data from this
link. It is a .sql file that can be imported
directly to PostgreSQL. Refer to lecture
notes on importing a .sql file into
PostgreSQL.
9. A single zip has to be submitted (for you
convinence we have provided an empty
file that you should use).

a. The zip is structured such that upon

deflating, all submission files appear
under a directory with the student’s
entry number. For, suppose a
student’s entry number is
22XXCSXX123. In that case, the zip
submission should be named
22XXCSXX123.zip, and upon
deflating, all contained files should
be under the directory named
./22XXCSXX123 only (names should
be in uppercase). Your submission
might be rejected and not be
evaluated if you do not adhere to
these specifications.

b. There are four sections (each of

different difficulty levels) in the
assignment, i.e., ”Basic”, ”
Intermediate”, ”Advanced”, and
“Graph”. For this, we have created
four different folders named ”BASIC”,
:
 ”INTERMEDIATE”, ”ADVANCED”, and
“GRAPH”.

c. In each designated section for every

question, write your query in the .sql
file following the naming convention
<folder name>_<question
number>.sql. For instance, the
solution to question ”1” within the ”
Basic” section should be saved in a
file named ”BASIC_1.sql”. Similarly,
the response to question ”2” in the ”
Graph” section must be saved in a
file named ”GRAPH_2.sql”. Please
do not write comments or other text
except for an SQL query in the .sql
files. Note don’t start your queries
with %sql or %%sql, that is only
specific to notebook.

d. The final directory structure should

look like below:

22XXCSXX123/
│
BASIC/
│ BASIC_1.sql
│ BASIC_2.sql
│ ...
│
INTERMEDIATE/
│ INTERMEDIATE_1.sql
│ INTERMEDIATE_2.sql
│ ...
:
 │
ADVANCED/
│ ADVANCED_1.sql
│ ADVANCED_2.sql
│ ...
│
GRAPH/
GRAPH_1.sql
GRAPH_2.sql
...

Dataset
This dataset is structured into two primary
modules—hospital ("hosp") and intensive
care unit ("icu")—which together span tens
of thousands of patient encounters and
aggregate millions of individual records. We
highly suggest to also examine the schema
and data before writing your queries.
Hospital Module ("hosp"):

• Scope: Derived from a hospital-wide

electronic health record, this module
covers a broad spectrum of patient care
data primarily recorded during hospital
stays, with some information extending
to outpatient settings.
:
 • Data Included:

◦ Patient and admission details (e.g.,

basic demographics, admissions,
transfers)

◦ Laboratory measurements and

related metadata

◦ Microbiology culture results

◦ Provider orders and medication

administration details

◦ Medication prescriptions and

pharmacy information

◦ Billing data, including diagnostic and

procedure codes, billing events, and
service-related information

◦ Additional service-related records

ICU Module ("icu"):
:
 ICU Module ("icu"):

• Scope: Sourced from a clinical

information system specific to intensive
care, this module focuses on high-
resolution ICU data. It has been
organized into a star schema linking ICU
stays to a variety of detailed event
records.

• Data Included:

◦ Patient ICU stays and associated

metadata

◦ Records of intravenous fluids and

medications, including their
ingredients

◦ Patient outputs, procedures

performed, and charted observations

◦ Events captured as specific date/time

entries

• Structure: Each event record is linked by

stay and item identifiers for detailed
traceability across tables.

Description
hosp Module
:
 Table Name Description

admissions Information about each hospital admission (hadm_id), including admission a

admission type, location, etc.

d_hcpcs Dimension table describing codes used in hcpcsevents (mostly CPT codes)

d_icd_diagnoses Dimension table describing ICD-9/ICD-10 diagnosis codes used in diagnose

d_icd_procedures Dimension table describing ICD-9/ICD-10 procedure codes used in procedu

d_labitems Dimension table describing laboratory test items. Links to labevents.

diagnoses_icd ICD-9/ICD-10 diagnoses billed for each hospital admission. Links to d_icd_d

drgcodes Diagnosis-related group (DRG) codes associated with hospital stays.

emar Electronic Medication Administration Record (eMAR) data, documenting med

(barcode scanning at bedside).

emar_detail Detailed breakdown of each eMAR event, including dose given and route.

hcpcsevents Hospital billing events containing HCPCS codes. Links to d_hcpcs.

labevents Laboratory measurements and results from hospital-wide labs. Links to d_la

microbiologyevent Microbiology cultures and antibiotic sensitivities.

s

omr Online Medical Record data, often containing outpatient-type measurements

patients Core patient-level information (gender, anchor age, date of death).

pharmacy Detailed records of filled medications, including dosing information and stat
and emar via pharmacy_id.

poe Provider Order Entry (POE) records. General interface for entering patient or
consults, etc.).

poe_detail Supplemental detail (in an Entity-Attribute-Value format) for entries in poe.

prescriptions Medications ordered for a patient, including route, frequency, and identifyin
:
 prescriptions Medications ordered for a patient, including route, frequency, and identifyin
to pharmacy and emar.

procedures_icd ICD-9/ICD-10 procedure codes billed during hospital stays. Links to d_icd_p

provider Lists de-identified provider identifiers used across hosp (e.g., order_provide

services Tracks clinical services assigned to the patient (e.g., “Surgery,” “Cardiology

transfers Tracks physical movement of patients across different wards/units in the hos

icu Module

Table Name Description

caregiver De-identified caregiver identifiers for the ICU module.

chartevents The majority of ICU charted data (vital signs, ventilator settings, etc.). Links
itemid.

datetimeevents Date/time-type measurements charted in the ICU (e.g., insertion dates).

d_items Dimension table defining itemid for ICU measurements.

icustays Defines individual ICU stays, with admission and discharge times.

ingredientevents Ingredients used within the same solution for a drug (e.g., multiple additives

inputevents Continuous infusions or intermittent administrations (IV fluids, drips, etc.).

outputevents Output data (urine output, drain volumes) recorded in the ICU.

procedureevents Procedures documented during the ICU stay (e.g., mechanical ventilation, im
includes start/end times and caregiver info.
:
 Detailed Tables of Columns
Below is a reference table for each dataset
table. Columns appear in the following
format:

JavaScript

| Column Name | Data Type |

Description |

hosp Module

• admissions

Column Name Data Type Description

subject_id bigint Foreign key referencing the patients table (each p

hadm_id bigint Primary key for each hospital admission (unique p

admittime text Date and time the patient was admitted.

dischtime text Date and time the patient was discharged.

deathtime text If applicable, date and time of in-hospital death (of

admission_type text General classification of the admission’s urgency o

etc.).

admit_provider_id text De-identified ID of the provider who admitted the p

provider table if needed).

admission_location text Location of the patient before arriving (e.g., emerg

discharge_location text Location to which the patient was discharged (e.g.

insurance text Type of insurance covering the patient.

language text Recorded primary language of the patient.

:
 language text Recorded primary language of the patient.

marital_status text Patient’s marital status as documented.

race text Race documented at admission.

edregtime text Date/time the patient was registered in the emerge

edouttime text Date/time the patient left the emergency departme

hospital_expire_flag bigint Indicates if patient died during the admission (1) o

Keys and Links

• subject_id → patients.subject_id

• hadm_id is unique within this table

(defining each admission).

• d_hcpcs

Column Name Data Type Description

code text HCPCS/CPT code (five characters). Primary key fo

category double precision Broad numeric category for the code.

long_description text Longer descriptive text for the code.

short_description text Short descriptive text for the code.

Keys and Links

• Joins to hcpcsevents on hcpcs_cd =

d_hcpcs.code.

• d_icd_diagnoses
:
 Column Name Data Type Description

icd_code text ICD diagnosis code. Together with icd_version, thi

diagnosis.

icd_version bigint ICD version (9 or 10).

long_title text Full descriptive title for the ICD code (e.g., “Choler

Keys and Links

• Joins to diagnoses_icd on (icd_code,

icd_version).

• d_icd_procedures

Column Name Data Type Description

icd_code text ICD procedure code. Together with icd_version, fo

procedure.

icd_version bigint ICD version (9 or 10).

long_title text Descriptive name for the procedure code.

Keys and Links

• Joins to procedures_icd on (icd_code,

icd_version).

• d_labitems

Column Name Data Type Description

itemid bigint Primary key. Unique identifier for a laboratory con

label text Human-readable description of the lab test (e.g., “

fluid text The substance on which the measurement was tak

:
 fluid text The substance on which the measurement was tak

category text Higher-level grouping for the lab measurement (e.g

“ABG”).

Keys and Links

• Joins to labevents on itemid.

• diagnoses_icd

Column Name Data Type Description

subject_id bigint Foreign key referencing patients.

hadm_id bigint Foreign key referencing admissions.

seq_num bigint Order/priority of the diagnosis for billing purposes

icd_code text ICD code for the diagnosis.

icd_version bigint ICD version (9 or 10).

Keys and Links

• Joins to d_icd_diagnoses on (icd_code,

icd_version).

• drgcodes

Column Name Data Type Description

subject_id bigint Foreign key referencing patients.

:
 hadm_id bigint Foreign key referencing admissions.

drg_type text The DRG ontology used.

drg_code bigint DRG code associated with the stay.

description text Description of the DRG code.

drg_severity double precision Severity of illness rating linked to the DRG.

drg_mortality double precision Mortality risk rating linked to the DRG.

• emar

Column Name Data Type Description

subject_id bigint Foreign key referencing patients.

hadm_id double precision Foreign key referencing admissions.

emar_id text Unique identifier for the eMAR entry.

emar_seq bigint Consecutive integer that orders eMAR entries chro

poe_id text Foreign key referencing poe. Links to the provider

pharmacy_id double precision Foreign key referencing pharmacy.

enter_provider_id text De-identified ID of the provider entering the eMAR

representing the provider who entered the medica
into the database)

charttime text Time the medication administration was charted.

medication text Name of the medication administered.

event_txt text High-level info about administration status (e.g., “A

scheduletime text Time the administration was scheduled, if available

:
 scheduletime text Time the administration was scheduled, if available

storetime text Time the administration was documented in the sy

• emar_detail

Column Name Data Type Description

subject_id bigint Foreign key referencing patients.

emar_id text Foreign key referencing emar. Unique ID for each

emar_seq bigint Consecutive integer which sorts eMAR entries.

parent_field_ordinal double precision Identifies multiple doses/formulary items for the sa

administration_type text Type of administration (e.g., “IV Bolus,” “Medicatio

pharmacy_id double precision Foreign key referencing pharmacy.

barcode_type text Type of barcode scanned (if any).

reason_for_no_barco text Reason no barcode was scanned if barcode scanni

de

complete_dose_not_ text Indicator if the full dose was not given.

given

dose_due, text The expected dose and units for the administration
dose_due_unit

dose_given, text The actual dose administered and units.

dose_given_unit

will_remainder_of_d text Indicates if leftover dose will be administered later

ose_be_given

product_amount_giv double precision The actual amount of product administered.

en
:
 product_unit text The unit for the product amount.

product_code, text Code/description for the scanned product.

product_description

prior_infusion_rate double precision Infusion rate prior to this administration.

infusion_rate double precision The new infusion rate.

infusion_rate_adjust text Indicates if the rate has been adjusted.

ment

infusion_rate_adjust double precision Amount of the rate adjustment.

ment_amount

infusion_rate_unit text The unit for the infusion rate (e.g., mL/hr).

route text Route of administration (e.g., IV, Oral).

infusion_complete text Whether the infusion completed as planned.

completion_interval text Additional time or instructions for completing the i

new_iv_bag_hung text Indicates if a new IV bag was hung.

continued_infusion_i text If the infusion continued after patient transfer.

n_other_location

restart_interval text Interval before restarting an infusion.

side, site text Documented side/site of administration (e.g., left a

non_formulary_visua text Documents if a non-formulary medication was visu

l_verification

• hcpcsevents

Column Name Data Type Description

subject_id bigint Foreign key referencing patients.

hadm_id bigint Foreign key referencing admissions.

:
 hadm_id bigint Foreign key referencing admissions.

chartdate text Date associated with the coded event.

hcpcs_cd text The HCPCS/CPT code; join with d_hcpcs.code.

seq_num bigint Sequence number for the code within a single adm

short_description text Short textual description for the code.

• labevents

Column Name Data Type Description

labevent_id bigint Unique identifier for each row in labevents.

subject_id bigint Foreign key referencing patients.

hadm_id double precision Foreign key referencing admissions.

specimen_id bigint Identifier grouping measurements from the same s

itemid bigint Lab item tested; join with d_labitems.itemid.

order_provider_id text Provider who ordered the lab test (de-identified).

charttime text Time the sample was taken or measurement made

storetime text Time the measurement was finalized and stored in

value text The lab test result in text form.

valuenum double precision Numeric value of the lab test result if applicable.

valueuom text Unit of measurement.

ref_range_lower double precision Lower bound of the normal range for this lab test, i
:
 ref_range_lower double precision Lower bound of the normal range for this lab test, i

ref_range_upper double precision Upper bound of the normal range for this lab test,

flag text Indicates if the result is abnormal or out of normal

priority text Lab priority (routine/stat).

comments text Additional notes regarding the measurement. Deid

• microbiologyevents

Column Name Data Type Description

microevent_id bigint Unique identifier for each row in microbiologyeve

subject_id bigint Foreign key referencing patients.

hadm_id double precision Foreign key referencing admissions.

micro_specimen_id bigint Identifier grouping measurements from the same m

order_provider_id text Provider ID for the microbiology test order.

chartdate text Date of the microbiology observation.

charttime text Time of the observation if available (NULL if only th

spec_itemid bigint Item ID describing the type of specimen.

spec_type_desc text Text describing the specimen (e.g., “Blood,” “Urine

test_seq bigint Sequence number differentiating multiple tests on

storedate text Date when the result was finalized.

storetime text Date/time when the result was finalized.

test_itemid bigint ID corresponding to the test performed.

test_name text Name of the microbiology test (e.g., “Gram Stain,”

org_itemid double precision ID of the organism that grew (if any).

:
 org_itemid double precision ID of the organism that grew (if any).

org_name text Name of the organism.

isolate_num double precision The isolate number within the specimen (multiple i

quantity double precision Approximate quantity of organism grown.

ab_itemid double precision ID of the antibiotic tested against the organism.

ab_name text Name of the antibiotic tested.

dilution_text text Free-text describing antibiotic dilution.

dilution_comparison text Comparison operator for the dilution (e.g. “>”, “<”)

dilution_value double precision Measured dilution value.

interpretation text Interpretation of antibiotic sensitivity (S = sensitive

etc.).

comments text Additional deidentified text about the culture or se

• omr

Column Name Data Type Description

subject_id bigint Foreign key referencing patients.

chartdate text Date on which the observation was recorded.

seq_num bigint Monotonically increasing integer to distinguish mu

the same day.

result_name text Description of the observation (e.g., “Blood Pressu

result_value text The value associated with the observation (e.g., “1

• patients
:
 Column Name Data Type Description

subject_id bigint Primary key. Unique patient identifier across the d

gender text Documented gender.

anchor_age bigint Age at the de-identified anchor year (capped at 90

anchor_year bigint Shifted year of the patient’s anchor date.

anchor_year_group text Range of years during which the anchor_year occu

dod text De-identified date of death for out-of-hospital mor

of hospital discharge).

• pharmacy

Column Name Data Type Description

subject_id bigint Foreign key referencing patients.

hadm_id bigint Foreign key referencing admissions.

pharmacy_id bigint Primary key within the pharmacy table, used to lin

poe_id text Reference to an order in poe.

starttime text Start time for the prescribed medication.

stoptime text Stop time for the prescribed medication.

medication text Name of the medication.

proc_type text Type of order (e.g., “IV Piggyback,” “Unit Dose”).

status text Whether the prescription is active, discontinued, e

entertime text Time at which the prescription was entered in the

verifiedtime text Time at which the order was verified.

route text Route of administration (e.g., IV, Oral).

:
 route text Route of administration (e.g., IV, Oral).

frequency text Frequency of administration (e.g., “Q6H,” “BID”).

disp_sched text Time(s) of day for scheduled administration (e.g.,

infusion_type text Code for type of infusion (e.g., ‘B’, ‘C’, ‘N’).

sliding_scale text Indicates whether a sliding scale is used (Y/N).

lockout_interval double precision Interval for patient-controlled analgesia (time bet

doses).

basal_rate double precision Basal continuous administration rate.

one_hr_max double precision Maximum one-hour dose for the medication.

doses_per_24_hrs double precision Number of doses expected per 24 hours.

duration double precision Numeric duration for this prescription.

duration_interval text Unit associated with the duration, e.g. “Doses,” “W

expiration_value double precision Numeric length of time until medication expires.

expiration_unit text Unit associated with the expiration (days, hours, e

expirationdate double precision The de-identified date of expiry.

dispensation text Source of dispensation.

fill_quantity double precision Quantity dispensed.

• poe
:
 Column Name Data Type Description

poe_id text Primary key within poe. Uniquely identifies the pr

poe_seq).

poe_seq bigint Monotonically increasing sequence for the order.

subject_id bigint Foreign key referencing patients.

hadm_id bigint Foreign key referencing admissions.

ordertime text Time at which the order was placed.

order_type text Broad classification of order (e.g., Lab, Medicatio

order_subtype text More specific subtype of the order (e.g., “Holter M

transaction_type text Action performed (e.g., “New,” “Change,” “D/C”).

discontinue_of_poe_i text If this order discontinues a previous order, refere

d

discontinued_by_poe text If this order was discontinued by another future o

_id poe_id.
:
 _id poe_id.

order_provider_id text Provider who placed this order (de-identified).

order_status text Whether the order is active or has been discontin

• poe_detail

Column Name Data Type Description

poe_id text Foreign key referencing poe. Unique identifier fo

poe_seq bigint Sequence number for the order.

subject_id bigint Foreign key referencing patients.

field_name text The label/name of the attribute for this order det

field_value text The value of that attribute (e.g., “Psychiatry,” “C

• prescriptions

Column Name Data Type Description

subject_id bigint Foreign key referencing patients.

hadm_id bigint Foreign key referencing admissions.

pharmacy_id bigint Foreign key referencing pharmacy.

poe_id text Links to poe.

:
 poe_id text Links to poe.

poe_seq double precision Monotonically increasing sequence for the presc

order_provider_id text Provider who placed the prescription order (de-i

starttime text Time the prescription was started.

stoptime text Time the prescription was stopped.

drug_type text Indicates if the drug is a main component, base,

drug text Name of the medication.

formulary_drug_cd text Hospital-specific code for the medication.

gsn text Generic Sequence Number (internal medication

ndc double precision National Drug Code identifier.

prod_strength text Strength/composition description (e.g., “12.5 mg

form_rx text Container format for the dose (e.g., “TABLET,” “V

dose_val_rx text Prescribed dose value.

dose_unit_rx text Prescribed dose unit.

form_val_disp text Amount of medication contained in a single form

form_unit_disp text Unit for the formulary dosage.

doses_per_24_hrs double precision Number of doses expected in 24 hours.

route text Route of administration (IV, Oral, etc.).

• procedures_icd

Column Name Data Type Description

subject_id bigint Foreign key referencing patients.

hadm_id bigint Foreign key referencing admissions.

:
 hadm_id bigint Foreign key referencing admissions.

seq_num bigint The priority of the procedure’s billing code.

chartdate text Date associated with the billed procedure.

icd_code text ICD code representing the procedure.

icd_version bigint ICD version (9 or 10).

Keys and Links

• Joins to d_icd_procedures on
(icd_code, icd_version).

• provider

Column Name Data Type Description

provider_id text Primary key. Unique de-identified provider ID u

• services

Column Name Data Type Description

subject_id bigint Foreign key referencing patients.

hadm_id bigint Foreign key referencing admissions.

transfertime text Time the service assignment changed from pre

prev_service text The previous service (if any).

curr_service text The current service. E.g., “SURG,” “MED,” “PSY

• transfers
:
 Column Name Data Type Description

subject_id bigint Foreign key referencing patients.

hadm_id double precision Foreign key referencing admissions.

transfer_id bigint Unique transfer event identifier (often used to

eventtype text Type of transfer event (e.g., ‘admit,’ ‘transfer,’

careunit text The unit or ward to which the patient was trans
etc.).

intime text Date/time the patient entered this unit.

outtime text Date/time the patient left this unit.

icu Module

• caregiver

Column Name Data Type Description

caregiver_id bigint Primary key in the ICU module. De-identified i

• chartevents

Column Name Data Type Description

subject_id bigint Foreign key referencing patients.

hadm_id bigint Foreign key referencing admissions.

stay_id bigint Foreign key referencing icustays.

caregiver_id double precision De-identified caregiver ID (reference to careg

charttime text Time the observation was recorded.

storetime text Time the observation was validated/entered.

:
 storetime text Time the observation was validated/entered.

itemid bigint Foreign key referencing d_items for a measur

value text Recorded value in text form.

valuenum double precision Numeric version of the recorded value (if appl

valueuom text Units of measurement.

warning double precision Indicates if a warning was triggered for this ch

• datetimeevents

Column Name Data Type Description

subject_id bigint Foreign key referencing patients.

hadm_id bigint Foreign key referencing admissions.

stay_id bigint Foreign key referencing icustays.

caregiver_id bigint Reference to caregiver.

charttime text Time of the event’s measurement.

storetime text Time data was validated/entered into the syst

itemid bigint Foreign key referencing d_items (for date/tim

value text The recorded date/time value (e.g., “2023-05

valueuom text Typically “Date” for these entries.

warning bigint Indicates whether a warning was documented

• d_items
:
 • d_items

Column Name Data Type Description

itemid bigint Primary key. Unique numeric identifier for eac

label text Human-readable label (e.g., “Heart Rate,” “R

abbreviation text Short abbreviation used in the ICU.

linksto text The name of the events table to which this ite
“inputevents”).

category text Category grouping (e.g., “Vital Signs,” “IV Me

unitname text Unit of measurement if consistent (e.g., “bpm

param_type text The type of parameter (numeric, text, date).

lownormalvalue double precision Recorded lower normal bound for numeric me

highnormalvalue double precision Recorded upper normal bound for numeric m

• icustays
:
 Column Name Data Type Description

subject_id bigint Foreign key referencing patients.

hadm_id bigint Foreign key referencing admissions.

stay_id bigint Primary key identifying a single ICU stay.

first_careunit text The first ICU care unit (e.g., MICU, SICU) the

last_careunit text The last ICU care unit the patient stayed in (c

intime text Date/time the patient was transferred into th

outtime text Date/time the patient was transferred out of

los double precision Length of stay in the ICU (days).

• ingredientevents
:
 Column Name Data Type Description

subject_id bigint Foreign key referencing patients.

hadm_id bigint Foreign key referencing admissions.

stay_id bigint Foreign key referencing icustays.

caregiver_id bigint Links to caregiver.

starttime text Start time for the infusion/ingredient admini

endtime text End time for the infusion/ingredient.

storetime text Time this entry was documented.

itemid bigint Foreign key referencing d_items.

amount double precision The total amount of the ingredient administe

amountuom text Units of the ingredient amount.

rate double precision The rate at which the ingredient was adminis

rateuom text Units of the rate (e.g., mL/hr).

:
 rateuom text Units of the rate (e.g., mL/hr).

orderid bigint Identifies multiple ingredients in the same so

linkorderid bigint Links repeated changes under the same bas

statusdescription text Status of the infusion (e.g., “Changed,” “Fin

originalamount bigint The initial planned total amount in the bag a

originalrate double precision The initial planned rate.

• inputevents

Column Name Data Type Description

subject_id bigint Foreign key referencing patients.

hadm_id bigint Foreign key referencing admissions.

stay_id bigint Foreign key referencing icustays.

caregiver_id bigint Reference to caregiver.

starttime text When the input began.

endtime text When the input ended.

storetime text Documentation time.

itemid bigint Foreign key referencing d_items.

amount double precision Amount administered (e.g., volume in mL).

amountuom text Units of amount.

rate double precision Rate of administration (e.g., mL/hr).

rateuom text Units of rate.

orderid bigint Identifies multiple items (drugs/fluids) in the

linkorderid bigint Links repeated modifications of the same or

:
 linkorderid bigint Links repeated modifications of the same or

ordercategoryname text Higher-level category for the order (e.g., “IV

secondaryordercategory text Secondary category if available.

name

ordercomponenttypedes text Description of the order component (additiv

cription

ordercategorydescriptio text More verbose description of the category.

n

patientweight double precision Patient weight (kg) at the time of the order.

totalamount double precision Total volume in the bag.

totalamountuom text Units of total volume.

isopenbag bigint Indicates if the bag was open.

continueinnextdept bigint Whether the infusion continued upon transf

statusdescription text Reason the infusion ended (e.g., “Changed,”

originalamount double precision The initial planned total amount at starttime

originalrate double precision The initial planned rate at starttime.

• outputevents

Column Name Data Type Description

subject_id bigint Foreign key referencing patients.

hadm_id bigint Foreign key referencing admissions.

stay_id bigint Foreign key referencing icustays.

caregiver_id bigint Reference to caregiver.

charttime text Time the output event was documented.

:
 charttime text Time the output event was documented.

storetime text Time data was finalized/entered.

itemid bigint Foreign key referencing d_items (e.g., type

value bigint The volume of output.

valueuom text Unit for the output volume (e.g., “mL”).

• procedureevents

Column Name Data Type Description

subject_id bigint Foreign key referencing patients.

hadm_id bigint Foreign key referencing admissions.

stay_id bigint Foreign key referencing icustays.

caregiver_id double precision Reference to caregiver.

starttime text Time the procedure began.

endtime text Time the procedure ended.

storetime text Time the procedure was documented in the

itemid bigint Foreign key referencing d_items (the type o

value double precision Duration or other numeric measure of the p

how long they lasted).

valueuom text Unit for the value (e.g., “min,” “hour”).

location text Specific physical location on the patient’s b

Arm”).

locationcategory text Higher-level category of the location (e.g., “

orderid bigint Links multiple procedure items in the same

linkorderid bigint References the original order if repeated.

:
 linkorderid bigint References the original order if repeated.

ordercategoryname text High-level name for the procedure order (e.

ordercategorydescriptio text More descriptive text for the order category

n

patientweight double precision Patient weight at the time.

isopenbag bigint Indicates if the procedure was from an open

continueinnextdept bigint If the procedure continued upon transfer.

statusdescription text Indicates final status (e.g., “FinishedRunnin

originalamount double precision Present but not clinically meaningful for ma

0 or 1).

originalrate bigint Also present but not clinically meaningful (o

Queries
Basic Queries
Note (for this section): In case of a tie,
output all the answers following the order.
Use the strings (along with the case) given in
the query for string comparison.
The format "X days, HH:MM:SS" is a
general format for interval differences. If the
duration is 24 hours or more, it is displayed
as "X days, HH:MM:SS". If the duration
is less than 24 hours, it is shown
as "HH:MM:SS"

1. List the subject_id of all female patients

:
 1. List the subject_id of all female patients
with anchor age more than 89.

output columns: subject_id

order by columns: subject_id

2. List the top 5 years in which the most
number of patients were admitted to
hospitals.

output columns: count, year

order by columns: count(descending),

year

3. List the hadm_id, gender, admission

duration(HH:MM:SS or X days,
HH:MM:SS) of all admissions which have
non-null dischtime. Admission duration
is defined as the difference between
dischtime and admittime.

output columns: hadm_id, gender,

duration

order by columns: duration, hadm_id

4. List the number of distinct medications
ordered by each valid(non-null) provider.

output columns: enter_provider_id,

count

order by columns: count(descending)

5. Find the number of distinct admissions
where the reason for the barcode not
being present is ‘Barcode Damaged’ and
:
 being present is ‘Barcode Damaged’ and
the marital status of the patient is not
‘MARRIED’.

output: count
6. For each patient, count the number of
times they have been admitted to
ICU(including 0 number of stays).

output columns: subject_id, count

order by columns: count, subject id

7. For every patient whose ‘dod’ is not null,

find their latest ‘hadm_id’.

output columns: subject_id,

latest_hadm_id, dod

order by columns: subject_id

8. List all the ‘pharmacy_id’ which are
present in the pharmacy but not written
in any prescriptions.

output columns: pharmacy_id

order by columns: pharmacy_id

9. List all unique ‘icd_code’, ‘icd_version’
values present in both’ diagnoses_icd’
and ‘procedures_icd’ tables.

output columns: icd_code, icd_version

order by columns: icd_code, icd_version

10. List the unique ‘hcpcs_cd’ and a short
:
 10. List the unique ‘hcpcs_cd’ and a short
description of all hcpcs events related to
‘Hospital observation’. You can assume
that an event is related to x if its
description contains x(case-insensitive).

output columns: hcpcs_cd,

short_description

order by columns: hcpcs_cd,

short_description

Intermediate Queries

Note (for this section): In case of a tie,

output all the answers following the order.
Use the strings (along with the case) given in
the query for string comparison.
The format "X days, HH:MM:SS" is a
general format for interval differences. If the
duration is 24 hours or more, it is displayed
as "X days, HH:MM:SS". If the duration
is less than 24 hours, it is shown
as "HH:MM:SS"

1. For each patient who has a non-NULL

value in the ‘dod’ (date of death) column,
write an SQL query to find the ‘hadm_id’
corresponding to their earliest
‘admittime’.

output columns: subject_id,

:
 latest_hadm_id hadm_id, dod

order by columns: subject_id

2. Find the average duration of
admissions(HH:MM:SS or X days,
HH:MM:SS) having diagnosis
icd_code=’4019’ and icd_version=’9’.
Admission duration is defined as the
difference between dischtime and
admittime.

output columns: avg_duration

3. For each caregiver_id, find the number of

procedure events, chart events, and
date-time events, documented by that
caregiver.

output columns: caregiver_id,

procedureevents_count,
chartevents_count,
datetimeevents_count

order by columns: caregiver_id,

procedureevents_count,
chartevents_count,
datetimeevents_count
4. Find out if any patient has been
diagnosed with an ‘infection’ in multiple
admissions in the same year(on the basis
of admission time). You can assume that
the title of such a diagnosis will contain
the word ‘infection’ (case insensitive).
:
 the word ‘infection’ (case insensitive).

output columns: subject_id,

count_admissions, year

order by columns: year,

count_admissions(descending),
subject_id

5. List all the patients who had an

admission type of ‘URGENT’ and died in
the same admission(use
hospital_expire_flag). Find how many
ICD procedures were ordered and how
many diagnoses were recorded for that
patient in that admission.

output columns: subject_id, hadm_id,

count_procedures, count_diagnoses

order by columns: subject_id, hadm_id,

count_procedures(descending),
count_diagnoses(descending)
6. Find the average BMI (kg/m2) of patients
who were prescribed the medications
‘OxyCODONE (Immediate Release)’ and
‘Insulin’ both (case sensitive). Submit
the results with exactly 10 places after
the decimal. [Hint - use hosp.omr table]
:
 the decimal. [Hint - use hosp.omr table]

output columns: avg_BMI

7. Find the patients who had the same
diagnoses when they were admitted for
the first time and most recently(use
admittime). Find the percentage
distribution of these patients on the
basis of their gender. Round off to 2
decimal places.

output columns: gender, percentage

order by columns:
percentage(descending)

8. Calculate the average admission duration

of hospital stays (HH:MM:SS or X days,
HH:MM:SS) for patients across all unique
admissions. Admission duration is
defined as the difference between
dischtime and admittime.

output columns: subject_id,

avg_duration

order by columns: subject_id

9. List the patients who were prescribed
the same medication (same
pharmacy_id) multiple times. List their
‘subject_id’ in the order(descending) of
the number of times a medication was
prescribed to the same patient. If the
count is same, order by subject_id,
pharmacy_id.
:
 output columns: subject_id,
pharmacy_id

10. List the patients who were diagnosed

with a kidney disease in their first
admission and readmitted after this
diagnoses. You can assume that the long
title of such a diagnosis will contain the
word ‘kidney’(case insensitive). List the
100 patients who were admitted most
recently(based on the ‘admittime’ of
their first admission)

output columns: subject_id

order by columns: subject_id

Advanced Queries
Note (for this section): In case of a tie,
output all the answers following the order.
Use the strings (along with the case) given in
:
 the query for string comparison.
For this section use the format “YYYY-MM-
DD HH:MM:SS” to display the time gap. You
may use the age() operator and extract year,
month and day from it. Make sure to stick to
the exact format.

1. Find patients whose diagnoses (from

diagnoses_icd ) changed across at least
three admissions, where the set of
diagnoses( icd_code ) in each admission
is different from those in another
admission or their prescribed
medications (from prescriptions ) must
have changed (based on the set of drug
names) at least three times between
admissions.

output columns: subject_id,

total_admissions,
num_distinct_diagnoses_set_count,
num_distinct_medications_set_count
order by columns: total_admissions
DESC,
num_distinct_diagnoses_set_count
DESC, subject_id ASC
:
 DESC, subject_id ASC
2. Identify patients (along with hadm_no )
who had microbiologyevents indicating
resistance to at least two different
antibiotics ( micro_specimen_id ) within a
single hospital admission, with the
antibiotics marked as 'R'
( interpretation ) in the
microbiologyevents table. Additionally,
check if these patients were later
transferred to the ICU( icustays ) during
that hospital stay, calculate their total
ICU length of stay in hours(upto 2
decimal places) for that admission. If a
patient did not have an ICU stay, set the
ICU length of stay to 0. Ensure to handle
only valid admissions (i.e., non-null
hadm_id , if hadm_id is null then do not
include in answer) and check whether
the patient experienced in-hospital
mortality in that admission by verifying if
the discharge_location field is marked
as 'DIED'. Output the 1 if patient died else
0.

Output columns: subject_id, hadm_id,

resistant_antibiotic_count,
icu_length_of_stay_hours, and
died_in_hospital.

ordered by columns:
died_in_hospital(descending),
resistant_antibiotic_count(descending)
,icu_length_of_stay_hours(descending),
:
 ,icu_length_of_stay_hours(descending),
subject_id(ascending),
hadm_id(ascending).

3. Find patients who underwent

multiple(more than one) distinct
procedures( icd_code ) from
procedures_icd for any admission and
were also diagnosed with icd_code
starting with "T81" from diagnoses_icd
in any admission. From those selected,
find out who has the higher-than-or-
equal to the average number of transfers
per admission among themselves.

output columns: subject_id,

distinct_procedures_count,
average_transfers

order by columns:
average_transfers(Descending),
distinct_procedures_count
(Descending), subject_id (Ascending),
hadm_id (Ascending)
:
 hadm_id (Ascending)
4. Find the patient having the longest chain
of transfers for an
admission( hadm_id ). For that patient
output all the chains of transfers for each
hospital admission. If more than one
patient has the longest chain output
them all.

output columns: subject_id, hadm_id,

[transfers]

order by: len([transfers]) Ascending,

hadm_id (Ascending), subject_id
(Ascending)
5. Find patients - admission pair whose at
least one procedure_icd has icd_code
starts with "0", "1", or "2". Keep the
patient - admission if they received
medications the same day or the next
day of the any procedure (for the same
admission). Ensure that patients receive
at least two different types of
medications( drug ) during their entire
hospital stay. Calculate the time gap (use
the same format as
prescriptions.starttime between the
first procedure and the last medication
prescription(assume the procedure took
place at midnight) of the admission,
determine the number of distinct
icd_codes from diagnoses_icd and find
the number of distinct procedures
( ) they have gone through
: