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

Created on May 20, 2025

Prompt

Show the trend of drug approvals over the past 20 years

Chart

Query Results

SQL Query:

WITH years AS (
    -- Generate a series of years from 20 years ago to the current year
    SELECT generate_series(EXTRACT(YEAR FROM CURRENT_DATE)::integer - 20, EXTRACT(YEAR FROM CURRENT_DATE)::integer) AS approval_year
),
approvals_by_year AS (
    -- Count the number of approvals for each year within the last 20 years
    SELECT
        EXTRACT(YEAR FROM p.approval_date)::integer AS approval_year,
        COUNT(p.id) AS number_of_approvals
    FROM orange_book_products p
    WHERE
        p.approval_date IS NOT NULL -- Only count products with an approval date
        AND EXTRACT(YEAR FROM p.approval_date)::integer >= (EXTRACT(YEAR FROM CURRENT_DATE)::integer - 20) -- Filter for performance
    GROUP BY EXTRACT(YEAR FROM p.approval_date)
)
-- Join the generated years with the approval counts to ensure all years in the range are included
SELECT
    y.approval_year,
    COALESCE(aby.number_of_approvals, 0) AS number_of_approvals -- Show 0 for years with no approvals
FROM years y
LEFT JOIN approvals_by_year aby ON y.approval_year = aby.approval_year
ORDER BY y.approval_year -- Order chronologically by year
LIMIT 20;

Results:

approval_year	number_of_approvals
2005	776
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

Chart Configuration

{
  "grid": {
    "left": "3%",
    "right": "4%",
    "bottom": "10%",
    "containLabel": true
  },
  "title": {
    "text": "Number of Approvals by Year"
  },
  "xAxis": {
    "data": [
      2005,
      2006,
      2007,
      2008,
      2009,
      2010,
      2011,
      2012,
      2013,
      2014,
      2015,
      2016,
      2017,
      2018,
      2019,
      2020,
      2021,
      2022,
      2023,
      2024
    ],
    "name": "Year",
    "type": "category",
    "nameGap": 30,
    "axisLabel": {
      "rotate": 30,
      "interval": 0
    },
    "nameLocation": "middle"
  },
  "yAxis": {
    "name": "Number of Approvals",
    "type": "value",
    "nameGap": 40,
    "nameLocation": "middle"
  },
  "legend": {
    "data": [
      "Number of Approvals"
    ],
    "bottom": 0
  },
  "series": [
    {
      "data": [
        776,
        892,
        1155,
        1170,
        1144,
        1010,
        1122,
        1257,
        1028,
        988,
        1287,
        1436,
        1840,
        1682,
        1831,
        1575,
        1494,
        1614,
        1904,
        1592
      ],
      "name": "Number of Approvals",
      "type": "line",
      "label": {
        "show": true,
        "position": "top"
      },
      "smooth": true
    }
  ],
  "tooltip": {
    "trigger": "axis"
  }
}