Compare generic and new drug applications by year

Created on May 6, 2025

Prompt

Chart

Query Results

SQL Query:

SELECT
    EXTRACT(YEAR FROM p.approval_date) AS approval_year,
    COUNT(CASE WHEN p.appl_type = 'A' THEN 1 END) AS generic_applications_count,
    COUNT(CASE WHEN p.appl_type = 'N' THEN 1 END) AS new_drug_applications_count
FROM
    orange_book_products p
WHERE
    p.approval_date IS NOT NULL
    AND p.appl_type IN ('A', 'N')
GROUP BY
    approval_year
ORDER BY
    approval_year
LIMIT 20;

Results:

approval_year	generic_applications_count	new_drug_applications_count
1982	3607	2898
1983	389	149
1984	384	266
1985	503	193
1986	680	172
1987	710	131
1988	667	260
1989	269	159
1990	92	113
1991	192	115
1992	212	179
1993	243	137
1994	214	122
1995	294	166
1996	359	243
1997	427	233
1998	355	198
1999	281	148
2000	459	192
2001	361	120

Chart Configuration

{
  "grid": {
    "left": "3%",
    "right": "4%",
    "bottom": "3%",
    "containLabel": true
  },
  "title": {
    "text": "Drug Application Counts by Year"
  },
  "xAxis": {
    "data": [
      "1982",
      "1983",
      "1984",
      "1985",
      "1986",
      "1987",
      "1988",
      "1989",
      "1990",
      "1991",
      "1992",
      "1993",
      "1994",
      "1995",
      "1996",
      "1997",
      "1998",
      "1999",
      "2000",
      "2001"
    ],
    "type": "category",
    "axisLabel": {
      "interval": 0
    }
  },
  "yAxis": {
    "type": "value"
  },
  "legend": {
    "data": [
      "Generic Applications",
      "New Drug Applications"
    ]
  },
  "series": [
    {
      "data": [
        3607,
        389,
        384,
        503,
        680,
        710,
        667,
        269,
        92,
        192,
        212,
        243,
        214,
        294,
        359,
        427,
        355,
        281,
        459,
        361
      ],
      "name": "Generic Applications",
      "type": "line",
      "smooth": true
    },
    {
      "data": [
        2898,
        149,
        266,
        193,
        172,
        131,
        260,
        159,
        113,
        115,
        179,
        137,
        122,
        166,
        243,
        233,
        198,
        148,
        192,
        120
      ],
      "name": "New Drug Applications",
      "type": "line",
      "smooth": true
    }
  ],
  "tooltip": {
    "trigger": "axis"
  }
}