OSINT-Driven Child Protection Analysis

Query 1: Total reports by category and year

SELECT
  EXTRACT(YEAR FROM incident_date) AS year,
  category,
  COUNT(*) AS report_count
FROM child_protection_cases
WHERE is_verified = TRUE
GROUP BY year, category
ORDER BY year DESC, report_count DESC;

Query 2: YoY growth rate per region (2024 vs 2025)

WITH yearly AS (
  SELECT
    r.region_name,
    EXTRACT(YEAR FROM c.incident_date) AS year,
    COUNT(*) AS cnt
  FROM child_protection_cases c
  JOIN regions r ON c.region_id = r.region_id
  WHERE c.is_verified = TRUE
    AND c.category = 'grooming'
    AND EXTRACT(YEAR FROM c.incident_date) IN (2024, 2025)
  GROUP BY r.region_name, year
)
SELECT
  region_name,
  MAX(CASE WHEN year = 2024 THEN cnt END) AS reports_2024,
  MAX(CASE WHEN year = 2025 THEN cnt END) AS reports_2025,
  ROUND(
    (MAX(CASE WHEN year = 2025 THEN cnt END) -
     MAX(CASE WHEN year = 2024 THEN cnt END))
    / MAX(CASE WHEN year = 2024 THEN cnt END)::numeric * 100, 1
  ) AS yoy_growth_pct
FROM yearly
GROUP BY region_name
ORDER BY yoy_growth_pct DESC;

Query 3: Dashboard SQL generator — the dynamic query

SELECT
  r.region_name AS location,
  EXTRACT(YEAR FROM i.incident_date) AS year,
  i.category,
  COUNT(*) AS report_count
FROM child_protection_cases i
JOIN regions r ON i.region_id = r.region_id
WHERE i.incident_date BETWEEN '2019-01-01' AND '2025-12-31'
  AND i.is_verified = TRUE
GROUP BY r.region_name, year, i.category
ORDER BY year DESC, report_count DESC;

This is the same query generated by the "View SQL" toggle in the dashboard below. The year range and region filters change dynamically based on your selections.