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`):

```sql
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`.