Reports per age group

It will be interesting to see how the age groups correlate. The original line-listings contain several age group definitions that we split up and stored as integers representing the number of months for the lower and upper age group boundaries (when given) because the smallest age group we found is "0 to 2 months" and we wanted to make sure we can detect that age group as well.

So, what age groups do we have and what can we tell about them in relation to reports? Let's take a look at the substance table.

SELECT 
  age_group_lower_months/12 age_from,
  age_group_upper_months/12 age_to,
  COUNT(DISTINCT local_number)
FROM import.substance
GROUP BY
  age_group_lower_months,
  age_group_upper_months
ORDER BY COUNT(DISTINCT local_number) DESC;
age_fromage_tocount
18644769137
2231876
65852216231
85315824
02211532
311204186
1217203213
0035760

We can see that most reports are for the age group 18 to 64 years, followed by unspecified, followed by 65 to 85 years.

SELECT
  age_group_lower_months/12 age_from,
  age_group_upper_months/12 age_to,
  (age_group_upper_months - age_group_lower_months)/12 AS "range",
  COUNT(DISTINCT local_number)
FROM import.substance
GROUP BY age_group_lower_months, age_group_upper_months
ORDER BY age_group_lower_months DESC;
age_fromage_torangecount
2216231
85315824
6585202231876
1864464769137
12175211532
3118204186
021203213
00035760

Ranges of the age groups are not equal. This makes them incomparable as f.i. the age group 18 to 64 years ranges 46 years while other groups are way smaller.