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

Prompt

Show the trend of drug approvals over the past 20 years

Chart

Query Results

Query Results

SQL Query:

WITH YearlyApprovals AS (
    -- Calculate the number of approvals for each year within the last 20 calendar years
    SELECT
        EXTRACT(YEAR FROM p.approval_date) AS approval_year,
        COUNT(*) AS approval_count
    FROM
        orange_book_products p
    WHERE
        p.approval_date IS NOT NULL
        AND EXTRACT(YEAR FROM p.approval_date) BETWEEN EXTRACT(YEAR FROM CURRENT_DATE) - 19 AND EXTRACT(YEAR FROM CURRENT_DATE)
    GROUP BY
        EXTRACT(YEAR FROM p.approval_date)
),
AllYears AS (
    -- Generate a series of all calendar years for the last 20 years
    SELECT generate_series(
        EXTRACT(YEAR FROM CURRENT_DATE) - 19, -- Start year (20 years ago from current year)
        EXTRACT(YEAR FROM CURRENT_DATE)       -- End year (current year)
    ) AS year
)
-- Join the generated years with the calculated approval counts
-- Use COALESCE to show 0 for years with no approvals
SELECT
    ay.year AS approval_year,
    COALESCE(ya.approval_count, 0) AS approval_count
FROM
    AllYears ay
LEFT JOIN
    YearlyApprovals ya ON ay.year = ya.approval_year
ORDER BY
    ay.year -- Order chronologically
LIMIT 20; -- Limit to ensure only 20 rows (one for each year in the range)

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"
    ],
    "type": "category",
    "axisLabel": {
      "interval": 0
    }
  },
  "yAxis": {
    "type": "value"
  },
  "legend": {},
  "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"
    }
  ],
  "tooltip": {
    "trigger": "axis",
    "axisPointer": {
      "type": "shadow"
    }
  }
}