Work FAERS in SQL
The same database, the same question, three ways of collecting the rows — and three different answers about whether drug harm is rising.
Python · SQL · SQLite · openFDA API
The finding
One sampling method flattened a real decade-long doubling in reports to a straight line. Another crammed almost everything into a single year. Only counting every report, uncapped, showed what was actually happening.
Method
~3,000 openFDA reports loaded into SQLite, one row per drug–reaction pair, queried with GROUP BY, WHERE and COUNT(DISTINCT).
So what
Before trusting a trend, ask how the rows were chosen. A defensible number and a convenient number are often produced by the same query and a different sample — and the query is never the hard part.
Interactive
The same reports-per-year query gives three different trends depending on how the data was sampled.
Same query. Three samplings. Three answers.
One SQL query — reports per year. The chart it draws depends entirely on how I pulled the data. Only one of these is real.
The SQL never changed. How you collect the data decides what you're allowed to conclude from it.
Full write-up
What I did
I pulled approximately 3,000 adverse event reports from the openFDA API and loaded them into a SQLite database, structured so each row is one drug–reaction pair for one report. Then I queried it with SQL.
The queries were standard analytical patterns — GROUP BY, WHERE, COUNT(DISTINCT) — applied to pharmacovigilance questions: which drugs had the most reported reactions, which reactions appeared across the most distinct drugs, how results changed when filtering by seriousness or patient age.
The important lesson
The lesson wasn’t about SQL syntax; it was about sampling.
My first reports-per-year chart was a lie: the naive fetch (skip=0,100…, no filter) pulled one consecutive block from openFDA’s storage, so almost every report landed in 2014 — a slice, not a sample. I fixed it by taking 300 reports per year — but that made the trend dead flat, hiding the fact that real FAERS volume roughly doubles across the decade. Only counting every report with no cap shows the true rising trend. How you collect the data decides what you’re allowed to conclude from it — the query was never the hard part.
Stack
Python · SQL · SQLite · openFDA API