CREATE TABLE transfers (row_id NUMBER, subject_id NUMBER, hadm_id NUMBER, icustay_id NUMBER, eventtype CLOB, careunit CLOB, wardid NUMBER, intime TIME, outtime TIME)
CREATE TABLE cost (row_id NUMBER, subject_id NUMBER, hadm_id NUMBER, event_type CLOB, event_id NUMBER, chargetime TIME, cost NUMBER)
CREATE TABLE d_icd_diagnoses (row_id NUMBER, icd9_code CLOB, short_title CLOB, long_title CLOB)
CREATE TABLE procedures_icd (row_id NUMBER, subject_id NUMBER, hadm_id NUMBER, icd9_code CLOB, charttime TIME)
CREATE TABLE prescriptions (row_id NUMBER, subject_id NUMBER, hadm_id NUMBER, startdate TIME, enddate TIME, drug CLOB, dose_val_rx CLOB, dose_unit_rx CLOB, route CLOB)
CREATE TABLE microbiologyevents (row_id NUMBER, subject_id NUMBER, hadm_id NUMBER, charttime TIME, spec_type_desc CLOB, org_name CLOB)
CREATE TABLE admissions (row_id NUMBER, subject_id NUMBER, hadm_id NUMBER, admittime TIME, dischtime TIME, admission_type CLOB, admission_location CLOB, discharge_location CLOB, insurance CLOB, language CLOB, marital_status CLOB, ethnicity CLOB, age NUMBER)
CREATE TABLE outputevents (row_id NUMBER, subject_id NUMBER, hadm_id NUMBER, icustay_id NUMBER, charttime TIME, itemid NUMBER, value NUMBER)
CREATE TABLE patients (row_id NUMBER, subject_id NUMBER, gender CLOB, dob TIME, dod TIME)
CREATE TABLE diagnoses_icd (row_id NUMBER, subject_id NUMBER, hadm_id NUMBER, icd9_code CLOB, charttime TIME)
CREATE TABLE d_labitems (row_id NUMBER, itemid NUMBER, label CLOB)
CREATE TABLE inputevents_cv (row_id NUMBER, subject_id NUMBER, hadm_id NUMBER, icustay_id NUMBER, charttime TIME, itemid NUMBER, amount NUMBER)
CREATE TABLE d_icd_procedures (row_id NUMBER, icd9_code CLOB, short_title CLOB, long_title CLOB)
CREATE TABLE chartevents (row_id NUMBER, subject_id NUMBER, hadm_id NUMBER, icustay_id NUMBER, itemid NUMBER, charttime TIME, valuenum NUMBER, valueuom CLOB)
CREATE TABLE icustays (row_id NUMBER, subject_id NUMBER, hadm_id NUMBER, icustay_id NUMBER, first_careunit CLOB, last_careunit CLOB, first_wardid NUMBER, last_wardid NUMBER, intime TIME, outtime TIME)
CREATE TABLE d_items (row_id NUMBER, itemid NUMBER, label CLOB, linksto CLOB)
CREATE TABLE labevents (row_id NUMBER, subject_id NUMBER, hadm_id NUMBER, itemid NUMBER, charttime TIME, valuenum NUMBER, valueuom CLOB)

what were the three most frequently ordered microbiology tests for patients that had previous fiber-optic bronchoscopy during the same month in 2104?
SELECT t3.spec_type_desc FROM (SELECT t2.spec_type_desc, DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS c1 FROM (SELECT admissions.subject_id, procedures_icd.charttime FROM procedures_icd JOIN admissions ON procedures_icd.hadm_id = admissions.hadm_id WHERE procedures_icd.icd9_code = (SELECT d_icd_procedures.icd9_code FROM d_icd_procedures WHERE d_icd_procedures.short_title = 'fiber-optic bronchoscopy') AND STRFTIME('%y', procedures_icd.charttime) = '2104') AS t1 JOIN (SELECT admissions.subject_id, microbiologyevents.spec_type_desc, microbiologyevents.charttime FROM microbiologyevents JOIN admissions ON microbiologyevents.hadm_id = admissions.hadm_id WHERE STRFTIME('%y', microbiologyevents.charttime) = '2104') AS t2 ON t1.subject_id = t2.subject_id WHERE t1.charttime < t2.charttime AND DATETIME(t1.charttime, 'start of month') = DATETIME(t2.charttime, 'start of month') GROUP BY t2.spec_type_desc) AS t3 WHERE t3.c1 <= 3
WITH "t2" AS (SELECT "admissions"."subject_id", "microbiologyevents"."spec_type_desc", "microbiologyevents"."charttime" FROM "microbiologyevents" JOIN "admissions" ON "admissions"."hadm_id" = "microbiologyevents"."hadm_id" WHERE STRFTIME('%y', "microbiologyevents"."charttime") = '2104'), "t3" AS (SELECT "t2"."spec_type_desc", DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS "c1" FROM "procedures_icd" JOIN "d_icd_procedures" ON "d_icd_procedures"."icd9_code" = "procedures_icd"."icd9_code" AND "d_icd_procedures"."short_title" = 'fiber-optic bronchoscopy' JOIN "admissions" ON "admissions"."hadm_id" = "procedures_icd"."hadm_id" JOIN "t2" "t2" ON "admissions"."subject_id" = "t2"."subject_id" AND "procedures_icd"."charttime" < "t2"."charttime" AND DATETIME("procedures_icd"."charttime", 'start of month') = DATETIME("t2"."charttime", 'start of month') WHERE STRFTIME('%y', "procedures_icd"."charttime") = '2104' GROUP BY "t2"."spec_type_desc") SELECT "t3"."spec_type_desc" FROM "t3" "t3" WHERE "t3"."c1" <= 3