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_from | age_to | count |
---|---|---|
18 | 64 | 4769137 |
2231876 | ||
65 | 85 | 2216231 |
85 | 315824 | |
0 | 2 | 211532 |
3 | 11 | 204186 |
12 | 17 | 203213 |
0 | 0 | 35760 |
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_from | age_to | range | count |
---|---|---|---|
2216231 | |||
85 | 315824 | ||
65 | 85 | 20 | 2231876 |
18 | 64 | 46 | 4769137 |
12 | 17 | 5 | 211532 |
3 | 11 | 8 | 204186 |
0 | 2 | 1 | 203213 |
0 | 0 | 0 | 35760 |
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.