Product details
The top count substance REVLIMID is a cancer medicine, the second most reported ENBREL is an anti-inflammatory drug. Let's take a look at some of the other products in detail. As an example we will take two products, Keytruda, also a cancer medicine and Inflectra another anti-inflammatory medicine.
Inflectra
Let's look for their product_id's:
SELECT * FROM
import.products
WHERE
name ILIKE '%flectra';
This gives us one results
product_id | name |
---|---|
435328 | INFLECTRA |
We will use the product_id's to dig deeper for information.
Seriousness
SELECT
product_id,
seriousness_criteria,
COUNT(distinct local_number)
FROM import.product_reaction
WHERE product_id = 435328
GROUP BY
product_id,
seriousness_criteria
ORDER BY COUNT(distinct local_number )desc limit 10;
product_id | seriousness_criteria | count |
---|---|---|
435328 | Other Medically Important Condition | 11125 |
435328 | 11101 | |
435328 | Caused/Prolonged Hospitalisation | 2439 |
435328 | Caused/Prolonged Hospitalisation, Other Medically Important Condition | 1833 |
435328 | Results in Death | 148 |
435328 | Life Threatening | 116 |
435328 | Results in Death, Other Medically Important Condition | 102 |
435328 | Disabling | 78 |
435328 | Life Threatening, Caused/Prolonged Hospitalisation | 54 |
435328 | Disabling, Other Medically Important Condition | 44 |
(10 rows)
Reporting range
We want to know when the first and the last reports where issued
SELECT
product_id,
min(report_date),
max(report_date),
count(distinct local_number)
FROM import.product_reaction
WHERE product_id = 435328
GROUP BY product_id;
product_id | min | max | count |
---|---|---|---|
435328 | 2014-08-20 | 2023-06-11 | 15106 |
Which shows that the first report on Inflectra was issued in 2014 and the last recently. It also shows that a total of 15106 reports on this product have been filed.
SELECT
product_id,
extract(year from date_trunc('year', report_date)) AS "year",
count(distinct local_number)
FROM import.product_reaction
WHERE product_id = 435328
GROUP BY product_id, date_trunc('year',report_date);
product_id | year | count |
---|---|---|
435328 | 2014 | 10 |
435328 | 2015 | 142 |
435328 | 2016 | 383 |
435328 | 2017 | 613 |
435328 | 2018 | 933 |
435328 | 2019 | 1263 |
435328 | 2020 | 1650 |
435328 | 2021 | 1896 |
435328 | 2022 | 2811 |
435328 | 2023 | 5405 |
(10 rows)
We see a year over year rise in the number of reports then a rise of reports that almost doubles in 2023 and given the database dates from July 2023, a probably even steeper increase.
Keytruda
Let's look for their product_id's:
SELECT * FROM
import.products
WHERE
name ILIKE '%truda';
This gives us one results
product_id | name |
---|---|
12129169 | KEYTRUDA |
We will use the product_id's to dig deeper for information.
Seriousness
SELECT
product_id,
seriousness_criteria,
COUNT(distinct local_number)
FROM import.product_reaction
WHERE product_id = 12129169
GROUP BY
product_id,
seriousness_criteria
ORDER BY COUNT(distinct local_number) desc limit 10;
product_id | seriousness_criteria | count |
---|---|---|
12129169 | Other Medically Important Condition | 14657 |
12129169 | 6453 | |
12129169 | Caused/Prolonged Hospitalisation, Other Medically Important Condition | 5917 |
12129169 | Caused/Prolonged Hospitalisation | 5462 |
12129169 | Results in Death, Other Medically Important Condition | 2725 |
12129169 | Results in Death, Caused/Prolonged Hospitalisation, Other Medically Important Condition | 1213 |
12129169 | Results in Death | 747 |
12129169 | Life Threatening, Caused/Prolonged Hospitalisation, Other Medically Important Condition | 620 |
12129169 | Results in Death, Life Threatening, Caused/Prolonged Hospitalisation, Other Medically Important Condition | 314 |
12129169 | Caused/Prolonged Hospitalisation, Disabling, Other Medically Important Condition | 255 |
Reporting range
We want to know when the first and the last reports where issued
SELECT
product_id,
min(report_date),
max(report_date),
count(distinct local_number)
FROM import.product_reaction
WHERE product_id = 12129169
GROUP BY product_id;
product_id | min | max | count |
---|---|---|---|
12129169 | 2014-10-13 | 2023-06-11 | 34024 |
Which shows that the first report on Keytruda was issued in 2014 and the last recently. It also shows that a total of 34024 reports on this product have been filed.
SELECT
product_id,
extract(year from date_trunc('year', report_date)) AS "year",
count(distinct local_number)
FROM import.product_reaction
WHERE product_id = 12129169
GROUP BY product_id, date_trunc('year',report_date);
product_id | year | count |
---|---|---|
12129169 | 2014 | 35 |
12129169 | 2015 | 317 |
12129169 | 2016 | 1101 |
12129169 | 2017 | 1896 |
12129169 | 2018 | 4066 |
12129169 | 2019 | 5167 |
12129169 | 2020 | 4055 |
12129169 | 2021 | 4843 |
12129169 | 2022 | 7054 |
12129169 | 2023 | 5490 |
We see a year over year rise in the number of reports with a dip in 2020, then a rise of reports that almost doubles in 2022 and given this database was created in July 2023 a possible rise in 2023 as well.