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

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_yearnumber_of_approvals
2005776
2006892
20071155
20081170
20091144
20101010
20111122
20121257
20131028
2014988
20151287
20161436
20171840
20181682
20191831
20201575
20211494
20221614
20231904
20241592
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"
  }
}