data-engineering/decade-analysis.md

Decade Analysis — Content Warning Trends

A worked analytics example over the live ReelShield cache. Computed against content_warnings.warnings_json joined to movies.year, grouped into decade buckets.

Snapshot date: 2026-05-08 Cache size at snapshot: 225 movies with generated warnings

---

1. Cache coverage by decade

Decade	Count
1940s	2
1950s	3
1960s	9
1970s	14
1980s	16
1990s	34
2000s	52
2010s	61
2020s	33

Sample sizes pre-1970s are small (2–9 films/decade); read those rows with appropriate caution.

---

2. Average severity per category per decade (0–3 scale)

Category	1940s	1950s	1960s	1970s	1980s	1990s	2000s	2010s	2020s
violence_gore	0.50	0.67	0.67	1.29	1.12	1.56	1.08	1.44	1.36
self_harm_suicide	0.00	0.00	0.00	0.21	0.25	0.15	0.00	0.21	0.09
miscarriage_pregnancy_loss	0.00	0.00	0.00	0.00	0.00	0.06	0.04	0.11	0.00
sexual_content_nudity	0.00	0.00	0.11	0.43	0.44	0.68	0.35	0.56	0.42
animal_abuse	0.00	0.00	0.22	0.21	0.25	0.21	0.17	0.31	0.27
substances	0.50	0.33	0.56	0.71	0.31	0.82	0.52	0.67	0.52
language	0.50	0.67	0.44	0.86	0.75	1.35	0.88	1.21	0.97
horror_intensity	0.50	0.33	0.00	0.93	0.81	0.94	0.79	1.20	0.88
flashing_lights	0.00	0.00	0.00	0.14	0.25	0.41	0.25	0.48	0.61

---

3. Most consistent decade-over-decade increase

flashing_lights — increased in 5 of 8 decade transitions, net change +0.61, and is the only category that ends at its all-time high (2020s = 0.61). This aligns with the rise of post-2000s digital editing and high-contrast color grading, and is exactly the trend that motivated putting the photosensitive epilepsy banner at the top of every movie page.

For comparison:

• sexual_content_nudity ties on transition count (5/8) but with a smaller net Δ.
• violence_gore has the largest net rise (+0.86) but more zigzag (only 4/8 transitions trending up).

---

4. Highest average severity, all categories combined

2010s — mean 0.689 across all 9 categories (n = 549 category-severity values), narrowly edging out the 1990s at 0.686.

---

Caveats

• Small sample sizes pre-1970s (2–9 movies/decade) make those rows noisy.
• The 2020s sample only covers cached entries up to ~2024–25; the decade is incomplete.
• Severities are Gemini-generated, not human-coded — the numbers reflect model perception of severity, which has known biases (e.g. it tends to under-report sexual content for older films).

---

Reproducing this analysis

The numbers above were generated by joining movies.year to the JSON in content_warnings.warnings_json. A representative query (SQLite, using json_extract):

SELECT
  (CAST(substr(m.year, 1, 3) AS INTEGER) * 10) || 's' AS decade,
  AVG(json_extract(c.warnings_json, '$.spoiler_free.violence_gore.severity'))     AS violence_gore,
  AVG(json_extract(c.warnings_json, '$.spoiler_free.self_harm_suicide.severity')) AS self_harm_suicide,
  AVG(json_extract(c.warnings_json, '$.spoiler_free.flashing_lights.severity'))   AS flashing_lights
  -- ... one AVG per category
FROM movies m
JOIN content_warnings c USING (tmdb_id)
WHERE m.year GLOB '[0-9][0-9][0-9][0-9]'
GROUP BY decade
ORDER BY decade;

The full per-category query lives in the team analysis notebook. Counts in §1 come from a simple GROUP BY decade over movies filtered to films that have an entry in content_warnings.