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_yeartotal_expirations
2025-01115
2025-0271
2025-03280
2025-04234
2025-05256
2025-06231
2025-07127
2025-0895
2025-0995
2025-10142
2025-11241
2025-12141
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"
  }
}