Compare drug substance vs drug product patents

Created on Apr 10, 2025

Prompt

Chart

Query Results

SQL Query:

SELECT
    EXTRACT(YEAR FROM patent_expire_date) AS category,
    COUNT(CASE WHEN drug_substance_flag = TRUE THEN 1 END) AS value,
    'Drug Substance' AS series,
    'Drug Substance Patents Expiring in ' || EXTRACT(YEAR FROM patent_expire_date) AS label,
    FALSE AS highlight
FROM
    orange_book_patents
WHERE drug_substance_flag = TRUE
GROUP BY
    EXTRACT(YEAR FROM patent_expire_date)

UNION ALL

SELECT
    EXTRACT(YEAR FROM patent_expire_date) AS category,
    COUNT(CASE WHEN drug_product_flag = TRUE THEN 1 END) AS value,
    'Drug Product' AS series,
    'Drug Product Patents Expiring in ' || EXTRACT(YEAR FROM patent_expire_date) AS label,
    FALSE AS highlight
FROM
    orange_book_patents
WHERE drug_product_flag = TRUE
GROUP BY
    EXTRACT(YEAR FROM patent_expire_date)
ORDER BY
    category,
    series;

Results:

category	value	series	label	highlight
2025	733	Drug Product	Drug Product Patents Expiring in 2025	false
2025	360	Drug Substance	Drug Substance Patents Expiring in 2025	false
2026	783	Drug Product	Drug Product Patents Expiring in 2026	false
2026	338	Drug Substance	Drug Substance Patents Expiring in 2026	false
2027	978	Drug Product	Drug Product Patents Expiring in 2027	false
2027	314	Drug Substance	Drug Substance Patents Expiring in 2027	false
2028	559	Drug Product	Drug Product Patents Expiring in 2028	false
2028	260	Drug Substance	Drug Substance Patents Expiring in 2028	false
2029	732	Drug Product	Drug Product Patents Expiring in 2029	false
2029	346	Drug Substance	Drug Substance Patents Expiring in 2029	false
2030	746	Drug Product	Drug Product Patents Expiring in 2030	false
2030	248	Drug Substance	Drug Substance Patents Expiring in 2030	false
2031	696	Drug Product	Drug Product Patents Expiring in 2031	false
2031	227	Drug Substance	Drug Substance Patents Expiring in 2031	false
2032	662	Drug Product	Drug Product Patents Expiring in 2032	false
2032	211	Drug Substance	Drug Substance Patents Expiring in 2032	false
2033	547	Drug Product	Drug Product Patents Expiring in 2033	false
2033	229	Drug Substance	Drug Substance Patents Expiring in 2033	false
2034	409	Drug Product	Drug Product Patents Expiring in 2034	false
2034	147	Drug Substance	Drug Substance Patents Expiring in 2034	false
2035	576	Drug Product	Drug Product Patents Expiring in 2035	false
2035	163	Drug Substance	Drug Substance Patents Expiring in 2035	false
2036	454	Drug Product	Drug Product Patents Expiring in 2036	false
2036	192	Drug Substance	Drug Substance Patents Expiring in 2036	false
2037	333	Drug Product	Drug Product Patents Expiring in 2037	false
2037	59	Drug Substance	Drug Substance Patents Expiring in 2037	false
2038	352	Drug Product	Drug Product Patents Expiring in 2038	false
2038	81	Drug Substance	Drug Substance Patents Expiring in 2038	false
2039	266	Drug Product	Drug Product Patents Expiring in 2039	false
2039	53	Drug Substance	Drug Substance Patents Expiring in 2039	false
2040	152	Drug Product	Drug Product Patents Expiring in 2040	false
2040	31	Drug Substance	Drug Substance Patents Expiring in 2040	false
2041	145	Drug Product	Drug Product Patents Expiring in 2041	false
2041	26	Drug Substance	Drug Substance Patents Expiring in 2041	false
2042	31	Drug Product	Drug Product Patents Expiring in 2042	false
2042	6	Drug Substance	Drug Substance Patents Expiring in 2042	false
2043	3	Drug Product	Drug Product Patents Expiring in 2043	false
2044	11	Drug Product	Drug Product Patents Expiring in 2044	false

Chart Configuration

{
  "grid": {
    "left": "3%",
    "right": "4%",
    "bottom": "3%",
    "containLabel": true
  },
  "title": {
    "top": "20px",
    "left": "center",
    "text": "Drug Substance vs. Drug Product Patent Expirations Over Time"
  },
  "xAxis": {
    "data": [
      "2025",
      "2026",
      "2027",
      "2028",
      "2029",
      "2030",
      "2031",
      "2032",
      "2033",
      "2034",
      "2035",
      "2036",
      "2037",
      "2038",
      "2039",
      "2040",
      "2041",
      "2042",
      "2043",
      "2044"
    ],
    "name": "Year",
    "type": "category",
    "axisLabel": {
      "rotate": 45,
      "interval": 0
    }
  },
  "yAxis": {
    "name": "Number of Patents",
    "type": "value",
    "boundaryGap": [
      0,
      0.1
    ]
  },
  "legend": {
    "top": "60px",
    "data": [
      "Drug Product",
      "Drug Substance"
    ]
  },
  "series": [
    {
      "data": [
        733,
        783,
        978,
        559,
        732,
        746,
        696,
        662,
        547,
        409,
        576,
        454,
        333,
        352,
        266,
        152,
        145,
        31,
        3,
        11
      ],
      "name": "Drug Product",
      "type": "bar"
    },
    {
      "data": [
        360,
        338,
        314,
        260,
        346,
        248,
        227,
        211,
        229,
        147,
        163,
        192,
        59,
        81,
        53,
        31,
        26,
        6,
        0,
        0
      ],
      "name": "Drug Substance",
      "type": "bar"
    }
  ],
  "tooltip": {
    "trigger": "axis",
    "formatter": "{b}: {c0} (Drug Substance), {c1} (Drug Product)",
    "axisPointer": {
      "type": "shadow"
    }
  }
}