Clinical trial: sites, patients, visits and adverse events

1. 1Is the adverse event rate different between the treatment and placebo arms?control_placebo: 1.4 events per patient, 77.6% with any; treatment: 3.15 events per patient, 94.3% with any.

   with ae as (
     select patient_id, count(*) as events,
            sum(case when serious then 1 else 0 end) as serious,
            sum(case when ctc_grade >= 3 then 1 else 0 end) as grade_3_plus
     from adverse_events
     group by patient_id
   )
   select p.arm,
          count(*) as patients,
          round(100.0 * sum(case when ae.events > 0 then 1 else 0 end) / count(*), 1) as with_any_ae_pct,
          round(1.0 * sum(coalesce(ae.events, 0)) / count(*), 2) as events_per_patient,
          sum(coalesce(ae.serious, 0)) as serious_events,
          sum(coalesce(ae.grade_3_plus, 0)) as grade_3_plus_events
   from patients p
   left join ae on ae.patient_id = p.patient_id
   group by p.arm
   order by p.arm

   arm	patients	with_any_ae_pct	events_per_patient	serious_events	grade_3_plus_events
   control_placebo	156	77.6	1.4	12	25
   treatment	158	94.3	3.15	30	71

2. 2Which biomarkers drift out of range after dosing?The largest rise after the first visit: Hemoglobin in treatment, 4.1% to 15.8% out of range.

   -- Out-of-range share at the first visit against every later visit, by arm.
   select l.analyte, p.arm,
          round(100.0 * sum(case when v.visit_number = 1 and l.is_out_of_range then 1 else 0 end)
                / nullif(sum(case when v.visit_number = 1 then 1 else 0 end), 0), 1) as first_visit_out_pct,
          round(100.0 * sum(case when v.visit_number > 1 and l.is_out_of_range then 1 else 0 end)
                / nullif(sum(case when v.visit_number > 1 then 1 else 0 end), 0), 1) as later_visits_out_pct
   from lab_biomarkers l
   join treatment_visits v on v.visit_id = l.visit_id
   join patients p on p.patient_id = l.patient_id
   group by l.analyte, p.arm
   order by l.analyte, p.arm

   analyte	arm	first_visit_out_pct	later_visits_out_pct
   ALT	control_placebo	6	6.1
   ALT	treatment	5.3	5.8
   Creatinine	control_placebo	6.7	5.8
   Creatinine	treatment	7.4	8.2
   Glucose	control_placebo	49	58
   Glucose	treatment	53.7	58.1
   Hemoglobin	control_placebo	4.8	5.2
   Hemoglobin	treatment	4.1	15.8
   Platelet count	control_placebo	4.1	5.3
   Platelet count	treatment	2	6.1
   White blood cell count	control_placebo	6.8	5.5
   White blood cell count	treatment	10.1	9.2

3. 3What share of patients withdraw or are lost, and when?active 44.9%; completed 40.1%; withdrawn 9.9% after a median 124 days; lost_to_followup 5.1% after a median 225 days.

   select status,
          count(*) as patients,
          round(100.0 * count(*) / (select count(*) from patients), 1) as share_pct,
          round(percentile_cont(0.5) within group (order by extract(epoch from (coalesce(withdrawn_at, lost_at) - enrolled_at)) / 86400), 0)
            as median_days_from_enrolment
   from patients
   group by status
   order by patients desc

   status	patients	share_pct	median_days_from_enrolment
   active	141	44.9
   completed	126	40.1
   withdrawn	31	9.9	124
   lost_to_followup	16	5.1	225

4. 4How does visit completion vary by site?Completion runs from 94.0% at Munich Cancer Institute to 98.3% at Boston University Hospital.

   select s.site_name, s.city, s.country,
          count(*) as scheduled_visits,
          round(100.0 * sum(case when v.visit_completed then 1 else 0 end) / count(*), 1) as completed_pct
   from treatment_visits v
   join sites s on s.site_id = v.site_id
   group by s.site_id, s.site_name, s.city, s.country
   order by completed_pct

   site_name	city	country	scheduled_visits	completed_pct
   Munich Cancer Institute	Bengaluru	India	218	94
   Madrid Community Hospital	Chicago	United States	225	94.7
   Boston Memorial Hospital	Bristol	United Kingdom	351	94.9
   Boston University Hospital	Rio de Janeiro	Brazil	221	95.9
   Paris Regional Medical Center	Amsterdam	Netherlands	447	96
   Toronto Regional Medical Center	New York	United States	101	97
   Toronto Cancer Institute	Amsterdam	Netherlands	1,070	97.1
   Toronto Memorial Hospital	Chicago	United States	391	97.4
   Boston University Hospital	Austin	United States	59	98.3

Every query runs on the files in the download as they are. ANSWERS.sql holds all of them; run it in DuckDB from the dataset's folder.

• Retail star schemaA dimensional model you can actually practise joins on: one fact table, four dimensions, 47,051 rows, every join resolving and every total known.47,051 rows · 5 tables
• Machine degradation100 machines monitored from commissioning to failure, with an exact remaining-useful-life label on every one of 23,118 readings.46,336 rows · 3 tables
• Ecommerce storefrontA year of orders across five joined tables, with best sellers, heavy buyers, a real Q4 peak and totals that reconcile to the cent.49,493 rows · 5 tables
• B2B SaaS subscription analyticsAccounts, seats, MRR, churn and support load, where company size actually drives the plan.42,240 rows · 5 tables
• Credit risk portfolio3,000 borrowers and 4,200 commercial loans, where a credit rating actually predicts whether the loan defaults, at the rate S&P's own data says it should.7,200 rows · 2 tables
• Network intrusion / netflow500 hosts and 20,000 labeled flows, where a port scan, a DDoS flood, and a brute-force attempt each carry the real statistical signature their MITRE ATT&CK technique actually leaves in traffic.20,500 rows · 2 tables