Show patents by the top 10 companies

Created on Apr 7, 2025

Prompt

Chart

Query Results

SQL Query:

SELECT
  obp.applicant_full_name,
  COUNT(DISTINCT obp2.patent_no) AS patent_count
FROM
  orange_book_products obp
JOIN
  orange_book_patents obp2
ON
  obp.appl_type = obp2.appl_type
  AND obp.appl_no = obp2.appl_no
  AND obp.product_no = obp2.product_no
GROUP BY
  obp.applicant_full_name
ORDER BY
  patent_count DESC
LIMIT 10;

Results:

applicant_full_name	patent_count
ABBVIE INC	248
AXSOME THERAPEUTICS INC	196
GILEAD SCIENCES INC	138
NOVARTIS PHARMACEUTICALS CORP	128
TAKEDA PHARMACEUTICALS USA INC	112
AZURITY PHARMACEUTICALS INC	104
BOEHRINGER INGELHEIM PHARMACEUTICALS INC	89
VERTEX PHARMACEUTICALS INC	83
PHARMACYCLICS LLC	82
ASTRAZENECA AB	73

Chart Configuration

{
  "grid": {
    "left": "3%",
    "right": "4%",
    "bottom": "3%",
    "containLabel": true
  },
  "color": [
    "#5470c6",
    "#91cc75",
    "#fac858",
    "#ee6666",
    "#73c0de",
    "#3ba272",
    "#fc8452",
    "#9a60b4",
    "#ea7ccc"
  ],
  "title": {
    "left": "center",
    "text": "Top 10 Companies by Number of Patents"
  },
  "xAxis": {
    "data": [
      "BOEHRINGER INGELHEIM PHARMACEUTICALS INC",
      "VERTEX PHARMACEUTICALS INC",
      "PHARMACYCLICS LLC",
      "ASTRAZENECA AB",
      "ABBVIE INC",
      "AXSOME THERAPEUTICS INC",
      "GILEAD SCIENCES INC",
      "NOVARTIS PHARMACEUTICALS CORP",
      "TAKEDA PHARMACEUTICALS USA INC",
      "AZURITY PHARMACEUTICALS INC"
    ],
    "name": "Company",
    "type": "category",
    "nameGap": 30,
    "axisLabel": {
      "rotate": 45,
      "interval": 0
    },
    "nameLocation": "middle"
  },
  "yAxis": {
    "name": "Number of Patents",
    "type": "value",
    "nameGap": 40,
    "axisLabel": {
      "formatter": "{value}"
    },
    "nameLocation": "middle"
  },
  "series": [
    {
      "id": "series1",
      "data": [
        "89",
        "83",
        "82",
        "73",
        "248",
        "196",
        "138",
        "128",
        "112",
        "104"
      ],
      "name": "Number of Patents",
      "type": "bar",
      "label": {
        "show": true,
        "position": "top",
        "formatter": "{c}"
      },
      "itemStyle": {
        "color": "#5470c6"
      }
    }
  ],
  "tooltip": {
    "trigger": "axis",
    "formatter": "{b}: {c}",
    "axisPointer": {
      "type": "shadow"
    }
  }
}