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