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

Created on Apr 11, 2025

Prompt

Show the trend of drug approvals over the past 20 years

Chart

Query Results

SQL Query:

SELECT
    EXTRACT(YEAR FROM approval_date) AS category,
    COUNT(*) AS value,
    'Drug Approvals' AS series,
    CAST(EXTRACT(YEAR FROM approval_date) AS VARCHAR) AS label,
    FALSE AS highlight
FROM
    orange_book_products
WHERE
    approval_date >= (CURRENT_DATE - INTERVAL '20 years')
GROUP BY
    category
ORDER BY
    category;

Results:

category	value	series	label	highlight
2005	122	Drug Approvals	2005	false
2006	892	Drug Approvals	2006	false
2007	1155	Drug Approvals	2007	false
2008	1170	Drug Approvals	2008	false
2009	1144	Drug Approvals	2009	false
2010	1010	Drug Approvals	2010	false
2011	1122	Drug Approvals	2011	false
2012	1257	Drug Approvals	2012	false
2013	1028	Drug Approvals	2013	false
2014	988	Drug Approvals	2014	false
2015	1287	Drug Approvals	2015	false
2016	1436	Drug Approvals	2016	false
2017	1840	Drug Approvals	2017	false
2018	1682	Drug Approvals	2018	false
2019	1831	Drug Approvals	2019	false
2020	1575	Drug Approvals	2020	false
2021	1494	Drug Approvals	2021	false
2022	1614	Drug Approvals	2022	false
2023	1904	Drug Approvals	2023	false
2024	1592	Drug Approvals	2024	false
2025	327	Drug Approvals	2025	false

Chart Configuration

{
  "color": [
    "#5470c6",
    "#91cc75",
    "#fac858",
    "#ee6666",
    "#73c0de",
    "#3ba272",
    "#fc8452",
    "#9a60b4",
    "#ea7ccc"
  ],
  "title": {
    "top": "20px",
    "left": "center",
    "text": "Trend of Drug Approvals Over the Past 20 Years"
  },
  "xAxis": {
    "data": [
      "2005",
      "2006",
      "2007",
      "2008",
      "2009",
      "2010",
      "2011",
      "2012",
      "2013",
      "2014",
      "2015",
      "2016",
      "2017",
      "2018",
      "2019",
      "2020",
      "2021",
      "2022",
      "2023",
      "2024",
      "2025"
    ],
    "name": "Year",
    "type": "category",
    "nameGap": 30,
    "boundaryGap": false,
    "nameLocation": "middle"
  },
  "yAxis": {
    "name": "Number of Approvals",
    "type": "value",
    "nameGap": 50,
    "nameLocation": "middle"
  },
  "legend": {
    "top": "bottom",
    "data": [
      "Drug Approvals"
    ]
  },
  "series": [
    {
      "data": [
        620,
        892,
        1155,
        1170,
        1144,
        1010,
        1122,
        1257,
        1028,
        988,
        1287,
        1436,
        1840,
        1682,
        1831,
        1575,
        1494,
        1614,
        1904,
        1592,
        327
      ],
      "name": "Drug Approvals",
      "type": "bar"
    }
  ],
  "toolbox": {
    "feature": {
      "restore": {},
      "dataZoom": {
        "yAxisIndex": "none"
      },
      "saveAsImage": {}
    }
  },
  "tooltip": {
    "trigger": "axis",
    "axisPointer": {
      "type": "cross",
      "label": {
        "backgroundColor": "#6a7985"
      }
    }
  },
  "dataZoom": [
    {
      "end": 100,
      "type": "inside",
      "start": 0
    },
    {
      "end": 100,
      "start": 0
    }
  ]
}