Show approval trends for the top 5 pharmaceutic...
Created on Apr 10, 2025
Prompt
Show approval trends for the top 5 pharmaceutical companies over the past 10 years
Chart
Query Results
Query Results
SQL Query:
WITH TopApplicants AS ( SELECT applicant_full_name FROM orange_book_products WHERE approval_date >= ( CURRENT_DATE - INTERVAL '10 year' ) GROUP BY applicant_full_name ORDER BY count(*) DESC LIMIT 5 ), ApprovalCounts AS ( SELECT EXTRACT( YEAR FROM approval_date ) AS approval_year, applicant_full_name, COUNT(*) AS approval_count FROM orange_book_products WHERE applicant_full_name IN ( SELECT applicant_full_name FROM TopApplicants ) AND approval_date >= ( CURRENT_DATE - INTERVAL '10 year' ) GROUP BY EXTRACT( YEAR FROM approval_date ), applicant_full_name ) SELECT approval_year AS category, approval_count AS value, applicant_full_name AS series, applicant_full_name || ' - ' || CAST(approval_year AS VARCHAR) || ': ' || CAST(approval_count AS VARCHAR) AS label, FALSE AS highlight FROM ApprovalCounts ORDER BY applicant_full_name, approval_year;
Results:
category | value | series | label | highlight |
---|---|---|---|---|
2015 | 11 | ALEMBIC PHARMACEUTICALS LTD | ALEMBIC PHARMACEUTICALS LTD - 2015: 11 | false |
2016 | 8 | ALEMBIC PHARMACEUTICALS LTD | ALEMBIC PHARMACEUTICALS LTD - 2016: 8 | false |
2017 | 24 | ALEMBIC PHARMACEUTICALS LTD | ALEMBIC PHARMACEUTICALS LTD - 2017: 24 | false |
2018 | 18 | ALEMBIC PHARMACEUTICALS LTD | ALEMBIC PHARMACEUTICALS LTD - 2018: 18 | false |
2019 | 57 | ALEMBIC PHARMACEUTICALS LTD | ALEMBIC PHARMACEUTICALS LTD - 2019: 57 | false |
2020 | 39 | ALEMBIC PHARMACEUTICALS LTD | ALEMBIC PHARMACEUTICALS LTD - 2020: 39 | false |
2021 | 37 | ALEMBIC PHARMACEUTICALS LTD | ALEMBIC PHARMACEUTICALS LTD - 2021: 37 | false |
2022 | 35 | ALEMBIC PHARMACEUTICALS LTD | ALEMBIC PHARMACEUTICALS LTD - 2022: 35 | false |
2023 | 40 | ALEMBIC PHARMACEUTICALS LTD | ALEMBIC PHARMACEUTICALS LTD - 2023: 40 | false |
2024 | 45 | ALEMBIC PHARMACEUTICALS LTD | ALEMBIC PHARMACEUTICALS LTD - 2024: 45 | false |
2025 | 6 | ALEMBIC PHARMACEUTICALS LTD | ALEMBIC PHARMACEUTICALS LTD - 2025: 6 | false |
2015 | 18 | ALKEM LABORATORIES LTD | ALKEM LABORATORIES LTD - 2015: 18 | false |
2016 | 8 | ALKEM LABORATORIES LTD | ALKEM LABORATORIES LTD - 2016: 8 | false |
2017 | 31 | ALKEM LABORATORIES LTD | ALKEM LABORATORIES LTD - 2017: 31 | false |
2018 | 12 | ALKEM LABORATORIES LTD | ALKEM LABORATORIES LTD - 2018: 12 | false |
2019 | 50 | ALKEM LABORATORIES LTD | ALKEM LABORATORIES LTD - 2019: 50 | false |
2020 | 38 | ALKEM LABORATORIES LTD | ALKEM LABORATORIES LTD - 2020: 38 | false |
2021 | 42 | ALKEM LABORATORIES LTD | ALKEM LABORATORIES LTD - 2021: 42 | false |
2022 | 28 | ALKEM LABORATORIES LTD | ALKEM LABORATORIES LTD - 2022: 28 | false |
2023 | 47 | ALKEM LABORATORIES LTD | ALKEM LABORATORIES LTD - 2023: 47 | false |
2024 | 17 | ALKEM LABORATORIES LTD | ALKEM LABORATORIES LTD - 2024: 17 | false |
2025 | 6 | ALKEM LABORATORIES LTD | ALKEM LABORATORIES LTD - 2025: 6 | false |
2015 | 40 | AUROBINDO PHARMA LTD | AUROBINDO PHARMA LTD - 2015: 40 | false |
2016 | 81 | AUROBINDO PHARMA LTD | AUROBINDO PHARMA LTD - 2016: 81 | false |
2017 | 73 | AUROBINDO PHARMA LTD | AUROBINDO PHARMA LTD - 2017: 73 | false |
2018 | 59 | AUROBINDO PHARMA LTD | AUROBINDO PHARMA LTD - 2018: 59 | false |
2019 | 27 | AUROBINDO PHARMA LTD | AUROBINDO PHARMA LTD - 2019: 27 | false |
2020 | 34 | AUROBINDO PHARMA LTD | AUROBINDO PHARMA LTD - 2020: 34 | false |
2021 | 30 | AUROBINDO PHARMA LTD | AUROBINDO PHARMA LTD - 2021: 30 | false |
2022 | 43 | AUROBINDO PHARMA LTD | AUROBINDO PHARMA LTD - 2022: 43 | false |
2023 | 115 | AUROBINDO PHARMA LTD | AUROBINDO PHARMA LTD - 2023: 115 | false |
2024 | 73 | AUROBINDO PHARMA LTD | AUROBINDO PHARMA LTD - 2024: 73 | false |
2025 | 4 | AUROBINDO PHARMA LTD | AUROBINDO PHARMA LTD - 2025: 4 | false |
2015 | 17 | MACLEODS PHARMACEUTICALS LTD | MACLEODS PHARMACEUTICALS LTD - 2015: 17 | false |
2016 | 39 | MACLEODS PHARMACEUTICALS LTD | MACLEODS PHARMACEUTICALS LTD - 2016: 39 | false |
2017 | 32 | MACLEODS PHARMACEUTICALS LTD | MACLEODS PHARMACEUTICALS LTD - 2017: 32 | false |
2018 | 11 | MACLEODS PHARMACEUTICALS LTD | MACLEODS PHARMACEUTICALS LTD - 2018: 11 | false |
2019 | 13 | MACLEODS PHARMACEUTICALS LTD | MACLEODS PHARMACEUTICALS LTD - 2019: 13 | false |
2020 | 4 | MACLEODS PHARMACEUTICALS LTD | MACLEODS PHARMACEUTICALS LTD - 2020: 4 | false |
2021 | 5 | MACLEODS PHARMACEUTICALS LTD | MACLEODS PHARMACEUTICALS LTD - 2021: 5 | false |
2022 | 15 | MACLEODS PHARMACEUTICALS LTD | MACLEODS PHARMACEUTICALS LTD - 2022: 15 | false |
2023 | 24 | MACLEODS PHARMACEUTICALS LTD | MACLEODS PHARMACEUTICALS LTD - 2023: 24 | false |
2024 | 78 | MACLEODS PHARMACEUTICALS LTD | MACLEODS PHARMACEUTICALS LTD - 2024: 78 | false |
2025 | 20 | MACLEODS PHARMACEUTICALS LTD | MACLEODS PHARMACEUTICALS LTD - 2025: 20 | false |
2015 | 1 | ZYDUS PHARMACEUTICALS USA INC | ZYDUS PHARMACEUTICALS USA INC - 2015: 1 | false |
2016 | 12 | ZYDUS PHARMACEUTICALS USA INC | ZYDUS PHARMACEUTICALS USA INC - 2016: 12 | false |
2017 | 146 | ZYDUS PHARMACEUTICALS USA INC | ZYDUS PHARMACEUTICALS USA INC - 2017: 146 | false |
2018 | 66 | ZYDUS PHARMACEUTICALS USA INC | ZYDUS PHARMACEUTICALS USA INC - 2018: 66 | false |
2019 | 47 | ZYDUS PHARMACEUTICALS USA INC | ZYDUS PHARMACEUTICALS USA INC - 2019: 47 | false |
2020 | 36 | ZYDUS PHARMACEUTICALS USA INC | ZYDUS PHARMACEUTICALS USA INC - 2020: 36 | false |
2021 | 13 | ZYDUS PHARMACEUTICALS USA INC | ZYDUS PHARMACEUTICALS USA INC - 2021: 13 | false |
2022 | 62 | ZYDUS PHARMACEUTICALS USA INC | ZYDUS PHARMACEUTICALS USA INC - 2022: 62 | false |
2023 | 93 | ZYDUS PHARMACEUTICALS USA INC | ZYDUS PHARMACEUTICALS USA INC - 2023: 93 | false |
2024 | 16 | ZYDUS PHARMACEUTICALS USA INC | ZYDUS PHARMACEUTICALS USA INC - 2024: 16 | false |
2025 | 7 | ZYDUS PHARMACEUTICALS USA INC | ZYDUS PHARMACEUTICALS USA INC - 2025: 7 | false |
Chart Configuration
{ "grid": { "left": "3%", "right": "4%", "bottom": "3%", "containLabel": true }, "title": { "top": "20px", "left": "center", "text": "Approval Trends for Top 5 Pharmaceutical Companies (Past 10 Years)" }, "xAxis": [ { "data": [ "2014", "2015", "2016", "2017", "2018", "2019", "2020", "2021", "2022", "2023" ], "name": "Year", "type": "category", "nameGap": 30, "boundaryGap": false, "nameLocation": "middle" } ], "yAxis": [ { "name": "Number of Approvals", "type": "value", "nameGap": 50, "nameLocation": "middle" } ], "legend": { "top": "10%", "data": [ "ALEMBIC PHARMACEUTICALS LTD", "ALKEM LABORATORIES LTD", "AUROBINDO PHARMA LTD", "MACLEODS PHARMACEUTICALS LTD", "ZYDUS PHARMACEUTICALS USA INC" ], "right": "10%" }, "series": [ { "data": [ 20, 8, 24, 18, 57, 39, 37, 35, 40, 45, 6 ], "name": "ALEMBIC PHARMACEUTICALS LTD", "type": "bar" }, { "data": [ 18, 8, 31, 12, 50, 38, 42, 28, 47, 17, 6 ], "name": "ALKEM LABORATORIES LTD", "type": "bar" }, { "data": [ 46, 81, 73, 59, 27, 34, 30, 43, 115, 73, 4 ], "name": "AUROBINDO PHARMA LTD", "type": "bar" }, { "data": [ 17, 39, 32, 11, 13, 4, 5, 15, 24, 78, 20 ], "name": "MACLEODS PHARMACEUTICALS LTD", "type": "bar" }, { "data": [ 1, 12, 146, 66, 47, 36, 13, 62, 93, 16, 7 ], "name": "ZYDUS PHARMACEUTICALS USA INC", "type": "bar" } ], "toolbox": { "feature": { "saveAsImage": {} } }, "tooltip": { "trigger": "axis", "axisPointer": { "type": "cross", "label": { "backgroundColor": "#6a7985" } } } }