Top 10 Consumer Products by 20-Year ER Injury Volume

Research question

Across the 838 consumer products with safety scores in PlainSafety, which carry the highest total estimated ER injuries over the most recent 20-year window, and how do those volumes break down by risk-level classification and hospitalization rate?

Methodology

We queried the PlainSafety product_categories table at server render time and pulled the columns product_name, group_name, total_injuries_20yr, avg_annual_injuries, pct_hospitalized, risk_level. The query ranks records by ss.total_injuries_20yr DESC and returns the top 10. Every numeric value rendered on this page derives from a live SELECT against the production product_categories table — no figure is hardcoded, and the table refreshes whenever the underlying U.S. Consumer Product Safety Commission dataset is reingested.

Column lineage: each field maps to a typed column in the product_categories table. Identifier columns carry the entity slug or code used elsewhere in PlainSafety; quantitative columns store values as exported by the U.S. Consumer Product Safety Commission (preserving the original measurement unit). Where the source publishes values in thousands of dollars, we render them via the standard PlainSafety money formatter that converts to billions or millions depending on magnitude. Where the source publishes raw integer counts, we render with thousand-separators preserved.

The ranking returned by this page reflects the most recent ETL run captured in the portal database. Every page load executes the same SQL against the read-only SQLite snapshot. Cache headers on the response are managed by the portal middleware: edge cache lifetime is bounded so a rebuilt dataset propagates within hours rather than days. The methodology page documents the full ETL pipeline, source vintage, and column lineage for PlainSafety.

Coverage and exclusions: rows are filtered by the WHERE clause on the primary query to remove null or zero values on the ranking column. U.S. Consumer Product Safety Commission occasionally suppresses values for reasons of confidentiality, sample size, or quality control; suppressed rows are excluded from this ranking by design rather than displayed as zeros. If the underlying source revises a value in a subsequent vintage, the revised value will appear on the next ETL run without changes to this page's source code.

Data provenance: the U.S. Consumer Product Safety Commission publishes the National Electronic Injury Surveillance System (NEISS) injury file once a year. Our pipeline pulls each annual release on its public availability date, loads it into a structured SQLite database, validates it, and renders every page directly from that database. Each new yearly vintage replaces the previous snapshot cleanly, so readers never encounter a partially updated page. The figures shown here cover the 2005 through 2024 NEISS record.

Schema design philosophy: PlainSafety normalizes upstream nested or wide-format records into long-format relational tables keyed by the natural identifier published by U.S. Consumer Product Safety Commission (entity codes, geographic FIPS identifiers, fiscal-year markers, program slugs). Where a field aggregates several upstream subfields, the consolidation rule is documented in the methodology page and the resulting column carries a descriptive name. Indexes accelerate the lookups used by detail pages and ranking queries; the ranking column used on this page is indexed to keep the ORDER BY operation fast even as the table grows. Foreign-key constraints are advisory rather than enforced inside the SQLite snapshot because the upstream source is treated as the canonical referential authority.

Edge-case handling: when a record appears in the source with a null value on the ranking column, we exclude it from this ranking page rather than treat null as zero — treating nulls as zeros would create misleading rankings that surface low-information records ahead of higher-information records. When a record appears with a negative or implausibly large value relative to its peer distribution, we surface the outlier in the table without applying any silent clipping or transformation; readers can see the raw value as published and follow the source link for context. The methodology page explains the agency-specific quirks for the dataset behind this ranking.

Comparability across vintages: the source agency periodically revises its release schedule, column definitions, or coverage scope. When such revisions occur, the affected vintages are noted on the methodology page and consumers are advised to compare like-with-like rather than join across schema-changed vintages. Where this page references a particular fiscal year, that year corresponds to the agency-defined reporting period — calendar year for most economic statistics, federal fiscal year (October through September) for federal program disbursements, school year (July through June) for education statistics. Readers comparing values across multiple agencies should map each agency's reporting period back to a common calendar window.

Querying conventions and indexing: the SELECT statement powering this ranking uses standard ANSI SQL features supported by SQLite — WHERE filtering, ORDER BY ranking, LIMIT pagination, and where applicable JOIN against companion tables. We avoid SQLite-specific syntax to keep queries portable. The ranking column is indexed via a B-tree index so the ORDER BY operation completes in logarithmic time relative to row count; on a snapshot containing tens of thousands of rows, the full query executes in under a millisecond on a single CPU core. Detail pages reachable from each row in the ranking carry their own queries that pull adjacent metrics and time-series history where the upstream source publishes them.

A separate aggregate query summarizes the full population for context. The aggregate runs against the same product_categories table without the LIMIT clause and computes a population count plus optional sum and mean. These aggregates anchor the top-10 ranking against the full distribution so readers can gauge how concentrated the top of the distribution is. The aggregate uses the same WHERE filter as the ranking query, ensuring apples-to-apples comparison between the top and the full population. Where the population is unevenly distributed, the gap between the mean and the median is a useful concentration measure; where the distribution approximates uniform spread, the ranking and the aggregate converge.

A secondary cut renders an adjacent dimension from the same dataset: a separate query against the product_categories table returns a related ranking that complements the primary table by surfacing a different metric. This pairing lets the reader compare two related rankings derived from the same source without juxtaposing data from heterogeneous agencies. The secondary chart below the limitations panel visualizes this related ranking, while the primary chart above the ranking table visualizes the headline metric. Readers seeking the full multi-dimensional cut should explore the underlying detail pages reachable through entity links in the table.

Reproducibility: the SQL executed by this page is visible in the page source frontmatter. A practitioner can copy the SELECT, point it at a local mirror of the PlainSafety SQLite database, and reproduce the exact ranking. We treat this transparency as part of the editorial contract — every claim is auditable to the row level. Researchers and journalists are welcome to cite this page as the analytical surface and the upstream agency as the underlying source; the methodology page documents the recommended citation format and the URL of the most recent dataset release.

Editorial governance: PlainSafety maintains an editorial standards document that codifies how rankings are constructed, how outliers are surfaced, how privacy-protected records are handled, and how corrections are processed when an entity disputes a value attributed to it. Subject-submitted corrections route through a defined intake process and are reconciled against the upstream record before publication; cosmetic corrections are recorded as overlay metadata while substantive corrections wait for the next official source release. A named editor reviews every ranking page before publication and signs off using the byline displayed at the top of this page. Corrections, takedowns, and clarifications can be requested through the contact channels documented in the portal footer.

Transparency commitments: PlainSafety publishes its full methodology, source registry, ETL pipeline status, and update history through dedicated pages reachable from the footer navigation. Visitors can trace any number on this page back to the underlying source row by following the entity link, inspecting the source URL referenced in the citation block, and comparing against the most recent vintage published by U.S. Consumer Product Safety Commission. Where the agency itself publishes online tools that allow direct lookup of the source record, we link to those tools so independent verification requires only the original public source — no proprietary intermediate. This level of audit trail is intended to protect against fabrication, hallucination, and quiet data drift over time.

See the methodology page for the complete ETL pipeline, source vintage, and column lineage.