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:

categoryvalueserieslabelhighlight
201511ALEMBIC PHARMACEUTICALS LTDALEMBIC PHARMACEUTICALS LTD - 2015: 11false
20168ALEMBIC PHARMACEUTICALS LTDALEMBIC PHARMACEUTICALS LTD - 2016: 8false
201724ALEMBIC PHARMACEUTICALS LTDALEMBIC PHARMACEUTICALS LTD - 2017: 24false
201818ALEMBIC PHARMACEUTICALS LTDALEMBIC PHARMACEUTICALS LTD - 2018: 18false
201957ALEMBIC PHARMACEUTICALS LTDALEMBIC PHARMACEUTICALS LTD - 2019: 57false
202039ALEMBIC PHARMACEUTICALS LTDALEMBIC PHARMACEUTICALS LTD - 2020: 39false
202137ALEMBIC PHARMACEUTICALS LTDALEMBIC PHARMACEUTICALS LTD - 2021: 37false
202235ALEMBIC PHARMACEUTICALS LTDALEMBIC PHARMACEUTICALS LTD - 2022: 35false
202340ALEMBIC PHARMACEUTICALS LTDALEMBIC PHARMACEUTICALS LTD - 2023: 40false
202445ALEMBIC PHARMACEUTICALS LTDALEMBIC PHARMACEUTICALS LTD - 2024: 45false
20256ALEMBIC PHARMACEUTICALS LTDALEMBIC PHARMACEUTICALS LTD - 2025: 6false
201518ALKEM LABORATORIES LTDALKEM LABORATORIES LTD - 2015: 18false
20168ALKEM LABORATORIES LTDALKEM LABORATORIES LTD - 2016: 8false
201731ALKEM LABORATORIES LTDALKEM LABORATORIES LTD - 2017: 31false
201812ALKEM LABORATORIES LTDALKEM LABORATORIES LTD - 2018: 12false
201950ALKEM LABORATORIES LTDALKEM LABORATORIES LTD - 2019: 50false
202038ALKEM LABORATORIES LTDALKEM LABORATORIES LTD - 2020: 38false
202142ALKEM LABORATORIES LTDALKEM LABORATORIES LTD - 2021: 42false
202228ALKEM LABORATORIES LTDALKEM LABORATORIES LTD - 2022: 28false
202347ALKEM LABORATORIES LTDALKEM LABORATORIES LTD - 2023: 47false
202417ALKEM LABORATORIES LTDALKEM LABORATORIES LTD - 2024: 17false
20256ALKEM LABORATORIES LTDALKEM LABORATORIES LTD - 2025: 6false
201540AUROBINDO PHARMA LTDAUROBINDO PHARMA LTD - 2015: 40false
201681AUROBINDO PHARMA LTDAUROBINDO PHARMA LTD - 2016: 81false
201773AUROBINDO PHARMA LTDAUROBINDO PHARMA LTD - 2017: 73false
201859AUROBINDO PHARMA LTDAUROBINDO PHARMA LTD - 2018: 59false
201927AUROBINDO PHARMA LTDAUROBINDO PHARMA LTD - 2019: 27false
202034AUROBINDO PHARMA LTDAUROBINDO PHARMA LTD - 2020: 34false
202130AUROBINDO PHARMA LTDAUROBINDO PHARMA LTD - 2021: 30false
202243AUROBINDO PHARMA LTDAUROBINDO PHARMA LTD - 2022: 43false
2023115AUROBINDO PHARMA LTDAUROBINDO PHARMA LTD - 2023: 115false
202473AUROBINDO PHARMA LTDAUROBINDO PHARMA LTD - 2024: 73false
20254AUROBINDO PHARMA LTDAUROBINDO PHARMA LTD - 2025: 4false
201517MACLEODS PHARMACEUTICALS LTDMACLEODS PHARMACEUTICALS LTD - 2015: 17false
201639MACLEODS PHARMACEUTICALS LTDMACLEODS PHARMACEUTICALS LTD - 2016: 39false
201732MACLEODS PHARMACEUTICALS LTDMACLEODS PHARMACEUTICALS LTD - 2017: 32false
201811MACLEODS PHARMACEUTICALS LTDMACLEODS PHARMACEUTICALS LTD - 2018: 11false
201913MACLEODS PHARMACEUTICALS LTDMACLEODS PHARMACEUTICALS LTD - 2019: 13false
20204MACLEODS PHARMACEUTICALS LTDMACLEODS PHARMACEUTICALS LTD - 2020: 4false
20215MACLEODS PHARMACEUTICALS LTDMACLEODS PHARMACEUTICALS LTD - 2021: 5false
202215MACLEODS PHARMACEUTICALS LTDMACLEODS PHARMACEUTICALS LTD - 2022: 15false
202324MACLEODS PHARMACEUTICALS LTDMACLEODS PHARMACEUTICALS LTD - 2023: 24false
202478MACLEODS PHARMACEUTICALS LTDMACLEODS PHARMACEUTICALS LTD - 2024: 78false
202520MACLEODS PHARMACEUTICALS LTDMACLEODS PHARMACEUTICALS LTD - 2025: 20false
20151ZYDUS PHARMACEUTICALS USA INCZYDUS PHARMACEUTICALS USA INC - 2015: 1false
201612ZYDUS PHARMACEUTICALS USA INCZYDUS PHARMACEUTICALS USA INC - 2016: 12false
2017146ZYDUS PHARMACEUTICALS USA INCZYDUS PHARMACEUTICALS USA INC - 2017: 146false
201866ZYDUS PHARMACEUTICALS USA INCZYDUS PHARMACEUTICALS USA INC - 2018: 66false
201947ZYDUS PHARMACEUTICALS USA INCZYDUS PHARMACEUTICALS USA INC - 2019: 47false
202036ZYDUS PHARMACEUTICALS USA INCZYDUS PHARMACEUTICALS USA INC - 2020: 36false
202113ZYDUS PHARMACEUTICALS USA INCZYDUS PHARMACEUTICALS USA INC - 2021: 13false
202262ZYDUS PHARMACEUTICALS USA INCZYDUS PHARMACEUTICALS USA INC - 2022: 62false
202393ZYDUS PHARMACEUTICALS USA INCZYDUS PHARMACEUTICALS USA INC - 2023: 93false
202416ZYDUS PHARMACEUTICALS USA INCZYDUS PHARMACEUTICALS USA INC - 2024: 16false
20257ZYDUS PHARMACEUTICALS USA INCZYDUS PHARMACEUTICALS USA INC - 2025: 7false
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"
      }
    }
  }
}