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 | 1 | ALEMBIC PHARMACEUTICALS LTD | ALEMBIC PHARMACEUTICALS LTD - 2015: 1 | 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 | 5 | ALKEM LABORATORIES LTD | ALKEM LABORATORIES LTD - 2015: 5 | 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 | 9 | AUROBINDO PHARMA LTD | AUROBINDO PHARMA LTD - 2015: 9 | 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 | 2 | MACLEODS PHARMACEUTICALS LTD | MACLEODS PHARMACEUTICALS LTD - 2015: 2 | 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 |
| 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"
}
}
}
}