Show the trend of drug approvals over the past ...
Created on May 6, 2025

Prompt

Show the trend of drug approvals over the past 20 years

Chart

Query Results

Query Results

SQL Query:

WITH YearSeries AS (
    -- Generate a series of years for the past 20 full years plus the current year
    SELECT generate_series(
        EXTRACT(YEAR FROM CURRENT_DATE - INTERVAL '19 years')::integer,
        EXTRACT(YEAR FROM CURRENT_DATE)::integer
    ) AS approval_year
),
ApprovalCounts AS (
    -- Count the number of approvals per year within the last 20 years
    SELECT
        EXTRACT(YEAR FROM p.approval_date)::integer AS approval_year,
        COUNT(*) AS approval_count
    FROM
        orange_book_products p
    WHERE
        p.approval_date IS NOT NULL
        AND p.approval_date >= CURRENT_DATE - INTERVAL '20 years' -- Filter for dates within the last 20 years
    GROUP BY
        EXTRACT(YEAR FROM p.approval_date)
)
-- Select the year and the corresponding approval count, including years with zero approvals
SELECT
    ys.approval_year,
    COALESCE(ac.approval_count, 0) AS approval_count
FROM
    YearSeries ys
LEFT JOIN
    ApprovalCounts ac ON ys.approval_year = ac.approval_year
ORDER BY
    ys.approval_year
LIMIT 20;

Results:

approval_yearapproval_count
2006892
20071155
20081170
20091144
20101010
20111122
20121257
20131028
2014988
20151287
20161436
20171840
20181682
20191831
20201575
20211494
20221614
20231904
20241592
2025327
Chart Configuration
{
  "grid": {
    "left": "3%",
    "right": "4%",
    "bottom": "3%",
    "containLabel": true
  },
  "title": {
    "text": "Approval Count by Year"
  },
  "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",
    "nameGap": 30,
    "axisLabel": {
      "rotate": 30,
      "interval": 0
    },
    "nameLocation": "middle"
  },
  "yAxis": {
    "name": "Count",
    "type": "value",
    "nameGap": 40,
    "nameLocation": "middle"
  },
  "legend": {
    "top": "top",
    "data": [
      "Approval Count"
    ]
  },
  "series": [
    {
      "data": [
        892,
        1155,
        1170,
        1144,
        1010,
        1122,
        1257,
        1028,
        988,
        1287,
        1436,
        1840,
        1682,
        1831,
        1575,
        1494,
        1614,
        1904,
        1592,
        327
      ],
      "name": "Approval Count",
      "type": "bar",
      "label": {
        "show": true,
        "position": "top"
      }
    }
  ],
  "tooltip": {
    "trigger": "axis",
    "axisPointer": {
      "type": "shadow"
    }
  }
}