Write a SQL query that returns patient_id and visit_date for patients who experienced at least one adverse event after receiving drug 'X' and had a lab_value exceeding the upper limit of normal (ULN) on that same visit.

Use the CIRCLES framework: Clarify the data model and criteria, Identify relevant tables and columns, Retrieve rows where drug_admin = 'X', Combine with adverse_event and lab_value > ULN using a JOIN or subquery, List patient_id and visit_date, Evaluate for duplicates with DISTINCT, Summarize the final SELECT. Step‑by‑step: 1) Clarify columns: patient_id, visit_date, drug_admin, adverse_event, lab_value, ULN. 2) Identify patients who received drug X. 3) Retrieve visits with adverse events. 4) Join with lab_value > ULN. 5) Use DISTINCT to avoid duplicates. 6) Return patient_id, visit_date. 7) Verify performance by checking execution plan.

SELECT DISTINCT p.patient_id, p.visit_date FROM patient_visits p JOIN drug_admin d ON p.patient_id = d.patient_id AND p.visit_date = d.visit_date JOIN adverse_events a ON p.patient_id = a.patient_id AND p.visit_date = a.visit_date WHERE d.drug_admin = 'X' AND a.adverse_event IS NOT NULL AND p.lab_value > p.uln ORDER BY p.patient_id, p.visit_date;

Explanation: The query joins the patient_visits table with drug_admin and adverse_events on patient_id and visit_date. It filters for drug X, ensures an adverse event exists, and checks that the lab_value exceeds the ULN. DISTINCT removes duplicate rows that could arise from multiple adverse events on the same visit. Ordering improves readability for downstream reporting.