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" } } }