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_idname
435328INFLECTRA

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_idseriousness_criteriacount
435328Other Medically Important Condition11125
43532811101
435328Caused/Prolonged Hospitalisation2439
435328Caused/Prolonged Hospitalisation, Other Medically Important Condition1833
435328Results in Death148
435328Life Threatening116
435328Results in Death, Other Medically Important Condition102
435328Disabling78
435328Life Threatening, Caused/Prolonged Hospitalisation54
435328Disabling, Other Medically Important Condition44

(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_idminmaxcount
4353282014-08-202023-06-1115106

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_idyearcount
435328201410
4353282015142
4353282016383
4353282017613
4353282018933
43532820191263
43532820201650
43532820211896
43532820222811
43532820235405

(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_idname
12129169KEYTRUDA

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_idseriousness_criteriacount
12129169Other Medically Important Condition14657
121291696453
12129169Caused/Prolonged Hospitalisation, Other Medically Important Condition5917
12129169Caused/Prolonged Hospitalisation5462
12129169Results in Death, Other Medically Important Condition2725
12129169Results in Death, Caused/Prolonged Hospitalisation, Other Medically Important Condition1213
12129169Results in Death747
12129169Life Threatening, Caused/Prolonged Hospitalisation, Other Medically Important Condition620
12129169Results in Death, Life Threatening, Caused/Prolonged Hospitalisation, Other Medically Important Condition314
12129169Caused/Prolonged Hospitalisation, Disabling, Other Medically Important Condition255

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_idminmaxcount
121291692014-10-132023-06-1134024

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_idyearcount
12129169201435
121291692015317
1212916920161101
1212916920171896
1212916920184066
1212916920195167
1212916920204055
1212916920214843
1212916920227054
1212916920235490

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.