Reports per country

We decided to investigate, as an example, what the distribution is of the reports per country. For this we used public.substance_cases_per_country and public.product_cases_per_country these tables contain the country, the product/substance code and the number of cases for which it is unclear if these are reports or another undisclosed property either generated, aggregated or summarized from reports or other, undisclosed information. We use a query to summarize content of the tables.

SELECT
  sum(value)
FROM public.substance_cases_per_country;

Result: 6641886 cases. This is roughly 64% of the unique number of reports we found earlier.

SELECT
  sum(value)
FROM public.product_cases_per_country;

Result: 1351575 cases.

Together these are roughly 8 of the 10 million records that are publically exposed through EMA, and only 8 of the15 million cases as stated in the EMA annual report towards the European commission. These numbers therefor have to be taken with a grain of salt, but we can check if there is a trend in what is publicly available.

get the top 10 countries for substance

SELECT
 country,
 sum(value),
 floor(sum(value)/(
    SELECT
      sum(value)
    FROM public.substance_cases_per_country
  )*100)::numeric::integer AS percentage
FROM public.substance_cases_per_country
GROUP BY country
ORDER BY sum DESC LIMIT 10;

For substances, France is contributing ~21% of all cases, Germany ~19%, Italy ~11%, United Kingdom and the Netherlands both ~7%, Spain ~5%, Sweden ~4%, Austria ~3%, Poland ~2%, and the rest of the countries less then 2%. We roughly conclude that five countries contribute ~63% of all cases for substances.

Get the top 10 countries for product

select
  country, sum(value),
  floor(sum(value)/(
    select
      sum(value)
    from public.product_cases_per_country
  )*100)::numeric::integer as percentage
from public.product_cases_per_country
GROUP BY country order by sum desc limit 10;

For products, Germany is contributing ~20% of all cases, France ~19%, Italy ~13%, United Kingdom ~10%, Netherlands and Spain both ~5%, Sweden ~3%, Austria, Greece, Belgium ~2% and the rest of the countries less then 2%. We roughly conclude that four countries contribute ~62% of all cases for products.

It is strange that between product cases and substance cases there are discrepancy in the percentages which slightly alters the sequence of the “leader board”. But roughly most contributions are by France/Germany, Italy, UK/Netherlands, Spain.

So there seem to be four to five countries that produce more then 60% of the total number of cases that are reported out of the total of 27 in the European Economic Area.

We do however want to state that absolute numbers should be related to population numbers as bigger countries can f.i. have more health professionals and more reporting but lower distribution when it comes to the size of the population. in further study these numbers could be correlated with the UN or World Bank population numbers. This however, is outside the scope of this example.