Show the trend of drug approvals over the past ...

Created on Apr 7, 2025

Prompt

Show the trend of drug approvals over the past 20 years

Chart

Query Results

SQL Query:

WITH CurrentYear AS (
    SELECT EXTRACT(YEAR FROM CURRENT_DATE) AS current_year
),
YearRange AS (
    SELECT generate_series((SELECT current_year FROM CurrentYear) - 19, (SELECT current_year FROM CurrentYear)) AS year
)
SELECT
    yr.year AS year,
    COALESCE(approval_counts.approval_count, 0) AS approval_count
FROM
    YearRange yr
LEFT JOIN (
    SELECT
        EXTRACT(YEAR FROM approval_date) AS approval_year,
        COUNT(*) AS approval_count
    FROM
        orange_book_products
    WHERE
        EXTRACT(YEAR FROM approval_date) >= (SELECT current_year FROM CurrentYear) - 19
    GROUP BY
        approval_year
) AS approval_counts ON yr.year = approval_counts.approval_year
ORDER BY
    yr.year;

Results:

year	approval_count
2006	892
2007	1155
2008	1170
2009	1144
2010	1010
2011	1122
2012	1257
2013	1028
2014	988
2015	1287
2016	1436
2017	1840
2018	1682
2019	1831
2020	1575
2021	1494
2022	1614
2023	1904
2024	1592
2025	327

Chart Configuration

{
  "title": {
    "left": "center",
    "text": "Drug Approvals Trend (Past 20 Years)"
  },
  "xAxis": {
    "data": [
      "2006",
      "2007",
      "2008",
      "2009",
      "2010",
      "2011",
      "2012",
      "2013",
      "2014",
      "2015",
      "2016",
      "2017",
      "2018",
      "2019",
      "2020",
      "2021",
      "2022",
      "2023",
      "2024",
      "2025"
    ],
    "name": "Year",
    "type": "category",
    "boundaryGap": false
  },
  "yAxis": {
    "name": "Number of Approvals",
    "type": "value"
  },
  "legend": {
    "top": "bottom",
    "data": [
      "Drug Approvals"
    ]
  },
  "series": [
    {
      "id": "series1",
      "data": [
        "892",
        "1155",
        "1170",
        "1144",
        "1010",
        "1122",
        "1257",
        "1028",
        "988",
        "1287",
        "1436",
        "1840",
        "1682",
        "1831",
        "1575",
        "1494",
        "1614",
        "1904",
        "1592",
        "327"
      ],
      "name": "Drug Approvals",
      "type": "line",
      "label": {
        "show": false,
        "position": "top"
      },
      "smooth": true,
      "emphasis": {
        "focus": "series"
      },
      "markLine": {
        "data": [
          {
            "name": "Avg",
            "type": "average"
          }
        ]
      },
      "areaStyle": {}
    }
  ],
  "toolbox": {
    "feature": {
      "restore": {},
      "dataZoom": {
        "yAxisIndex": "none"
      },
      "saveAsImage": {}
    }
  },
  "tooltip": {
    "trigger": "axis",
    "formatter": "Year: {b}<br/>Approvals: {c}"
  },
  "dataZoom": [
    {
      "end": 100,
      "type": "inside",
      "start": 0
    },
    {
      "end": 100,
      "start": 0
    }
  ],
  "visualMap": {
    "show": false,
    "pieces": [
      {
        "gt": 1500,
        "color": "rgba(255,0,0,0.7)"
      },
      {
        "gt": 1000,
        "lte": 1500,
        "color": "rgba(255,165,0,0.7)"
      },
      {
        "gt": 500,
        "lte": 1000,
        "color": "rgba(255,255,0,0.7)"
      },
      {
        "lt": 500,
        "color": "rgba(0,255,0,0.7)"
      }
    ],
    "outOfRange": {
      "color": "rgba(0,0,255,0.7)"
    }
  }
}