expering product per month this year
Created on May 6, 2025
Prompt
expering product per month this year
Chart
Query Results
Query Results
SQL Query:
WITH AllExpirationDates AS ( -- Combine patent and exclusivity expiration dates SELECT patent_expire_date AS expiration_date FROM orange_book_patents WHERE patent_expire_date IS NOT NULL UNION ALL SELECT exclusivity_date AS expiration_date FROM orange_book_exclusivities WHERE exclusivity_date IS NOT NULL ), FilteredExpirations AS ( -- Filter for expiration dates within the current year SELECT expiration_date FROM AllExpirationDates WHERE EXTRACT(YEAR FROM expiration_date) = EXTRACT(YEAR FROM CURRENT_DATE) ), MonthlyCounts AS ( -- Count expirations per month for the current year SELECT EXTRACT(MONTH FROM expiration_date)::integer AS month_num, COUNT(*) AS expiration_count FROM FilteredExpirations GROUP BY 1 ), CalendarMonths AS ( -- Generate all month numbers and YYYY-MM format for the current year SELECT EXTRACT(MONTH FROM gs)::integer AS month_num, TO_CHAR(gs, 'YYYY-MM') AS month_year FROM GENERATE_SERIES( date_trunc('year', CURRENT_DATE), date_trunc('year', CURRENT_DATE) + interval '11 months', '1 month' ) AS gs ) -- Select the month and the count, including months with zero expirations SELECT cm.month_year, COALESCE(mc.expiration_count, 0) AS total_expirations FROM CalendarMonths cm LEFT JOIN MonthlyCounts mc ON cm.month_num = mc.month_num ORDER BY cm.month_num LIMIT 20;
Results:
month_year | total_expirations |
---|---|
2025-01 | 115 |
2025-02 | 71 |
2025-03 | 280 |
2025-04 | 234 |
2025-05 | 256 |
2025-06 | 231 |
2025-07 | 127 |
2025-08 | 95 |
2025-09 | 95 |
2025-10 | 142 |
2025-11 | 241 |
2025-12 | 141 |
Chart Configuration
{ "grid": { "left": "3%", "right": "4%", "bottom": "3%", "containLabel": true }, "title": { "text": "Total Expirations by Month" }, "xAxis": { "data": [ "2025-01", "2025-02", "2025-03", "2025-04", "2025-05", "2025-06", "2025-07", "2025-08", "2025-09", "2025-10", "2025-11", "2025-12" ], "name": "Category", "type": "category", "nameGap": 30, "axisLabel": { "rotate": 30, "interval": 0 }, "nameLocation": "middle" }, "yAxis": { "name": "Value", "type": "value", "nameGap": 40, "nameLocation": "middle" }, "legend": { "top": "top", "data": [ "Total Expirations" ] }, "series": [ { "data": [ 115, 71, 280, 234, 256, 231, 127, 95, 95, 142, 241, 141 ], "name": "Total Expirations", "type": "line", "label": { "show": true, "position": "top" }, "smooth": true } ], "tooltip": { "trigger": "axis" } }