Which company has the most patents still in eff...
Created on Apr 10, 2025

Prompt

Which company has the most patents still in effect for non generic drugs

Chart

Query Results

Query Results

SQL Query:

SELECT
  obp.applicant_full_name AS series,
  COUNT(DISTINCT obp2.patent_no) AS value,
  obp.applicant_full_name AS label,
  CASE WHEN COUNT(DISTINCT obp2.patent_no) = (
    SELECT
      MAX(patent_count)
    FROM (
      SELECT
        obp_inner.applicant_full_name,
        COUNT(DISTINCT obp2_inner.patent_no) AS patent_count
      FROM orange_book_products AS obp_inner
      JOIN orange_book_patents AS obp2_inner
        ON obp_inner.appl_type = obp2_inner.appl_type
        AND obp_inner.appl_no = obp2_inner.appl_no
        AND obp_inner.product_no = obp2_inner.product_no
      WHERE
        obp_inner.type = 'RX' AND obp_inner.te_code NOT LIKE 'GN%' AND obp2_inner.patent_expire_date >= CURRENT_DATE
      GROUP BY
        obp_inner.applicant_full_name
    ) AS subquery
  ) THEN TRUE ELSE FALSE END AS highlight,
  'Company' AS category
FROM orange_book_products AS obp
JOIN orange_book_patents AS obp2
  ON obp.appl_type = obp2.appl_type
  AND obp.appl_no = obp2.appl_no
  AND obp.product_no = obp2.product_no
WHERE
  obp.type = 'RX' AND obp.te_code NOT LIKE 'GN%' AND obp2.patent_expire_date >= CURRENT_DATE
GROUP BY
  obp.applicant_full_name
ORDER BY
  value DESC
LIMIT 1;

Results:

seriesvaluelabelhighlightcategory
AMARIN PHARMACEUTICALS IRELAND LTD64AMARIN PHARMACEUTICALS IRELAND LTDtrueCompany
Chart Configuration
{
  "grid": {
    "left": "3%",
    "right": "4%",
    "bottom": "3%",
    "containLabel": true
  },
  "title": {
    "top": "20px",
    "left": "center",
    "text": "Companies with the Most Active Patents for Non-Generic Drugs"
  },
  "xAxis": {
    "data": [
      "Company"
    ],
    "name": "Number of Active Patents",
    "type": "value",
    "axisLabel": {
      "formatter": "{value}"
    }
  },
  "yAxis": {
    "data": [],
    "type": "category",
    "inverse": true,
    "axisLabel": {
      "rotate": 30,
      "interval": 0
    }
  },
  "legend": {
    "data": [
      "AMARIN PHARMACEUTICALS IRELAND LTD"
    ],
    "left": "center",
    "bottom": "5%",
    "orient": "horizontal"
  },
  "series": [
    {
      "data": [
        64
      ],
      "name": "AMARIN PHARMACEUTICALS IRELAND LTD",
      "type": "bar"
    }
  ],
  "tooltip": {
    "trigger": "axis",
    "axisPointer": {
      "type": "shadow"
    }
  }
}