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_year | number_of_approvals |
---|---|
2005 | 776 |
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 |
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" } }