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