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

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_year	approval_count
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
2025	327

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"
    }
  }
}