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:
| series | value | label | highlight | category |
|---|---|---|---|---|
| AMARIN PHARMACEUTICALS IRELAND LTD | 64 | AMARIN PHARMACEUTICALS IRELAND LTD | true | Company |
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"
}
}
}