Compare generic and new drug applications by year

Created on Apr 10, 2025

Prompt

Chart

Query Results

SQL Query:

SELECT
    EXTRACT(YEAR FROM approval_date) AS category,
    COUNT(*) AS value,
    CASE
        WHEN appl_type = 'A' THEN 'Generic'
        WHEN appl_type = 'N' THEN 'New Drug'
        ELSE 'Other'
    END AS series,
    CASE
        WHEN appl_type = 'A' THEN 'Generic'
        WHEN appl_type = 'N' THEN 'New Drug'
        ELSE 'Other'
    END AS label,
    FALSE AS highlight
FROM
    orange_book_products
WHERE appl_type IN ('A', 'N')
GROUP BY
    EXTRACT(YEAR FROM approval_date),
    appl_type
ORDER BY
    EXTRACT(YEAR FROM approval_date),
    appl_type;

Results:

category	value	series	label	highlight
1982	3607	Generic	Generic	false
1982	2898	New Drug	New Drug	false
1983	389	Generic	Generic	false
1983	149	New Drug	New Drug	false
1984	384	Generic	Generic	false
1984	266	New Drug	New Drug	false
1985	503	Generic	Generic	false
1985	193	New Drug	New Drug	false
1986	680	Generic	Generic	false
1986	172	New Drug	New Drug	false
1987	710	Generic	Generic	false
1987	131	New Drug	New Drug	false
1988	667	Generic	Generic	false
1988	260	New Drug	New Drug	false
1989	269	Generic	Generic	false
1989	159	New Drug	New Drug	false
1990	92	Generic	Generic	false
1990	113	New Drug	New Drug	false
1991	192	Generic	Generic	false
1991	115	New Drug	New Drug	false
1992	212	Generic	Generic	false
1992	179	New Drug	New Drug	false
1993	243	Generic	Generic	false
1993	137	New Drug	New Drug	false
1994	214	Generic	Generic	false
1994	122	New Drug	New Drug	false
1995	294	Generic	Generic	false
1995	166	New Drug	New Drug	false
1996	359	Generic	Generic	false
1996	243	New Drug	New Drug	false
1997	427	Generic	Generic	false
1997	233	New Drug	New Drug	false
1998	355	Generic	Generic	false
1998	198	New Drug	New Drug	false
1999	281	Generic	Generic	false
1999	148	New Drug	New Drug	false
2000	459	Generic	Generic	false
2000	192	New Drug	New Drug	false
2001	361	Generic	Generic	false
2001	120	New Drug	New Drug	false
2002	603	Generic	Generic	false
2002	206	New Drug	New Drug	false
2003	479	Generic	Generic	false
2003	174	New Drug	New Drug	false
2004	689	Generic	Generic	false
2004	190	New Drug	New Drug	false
2005	640	Generic	Generic	false
2005	136	New Drug	New Drug	false
2006	703	Generic	Generic	false
2006	189	New Drug	New Drug	false
2007	993	Generic	Generic	false
2007	162	New Drug	New Drug	false
2008	1012	Generic	Generic	false
2008	158	New Drug	New Drug	false
2009	968	Generic	Generic	false
2009	176	New Drug	New Drug	false
2010	858	Generic	Generic	false
2010	152	New Drug	New Drug	false
2011	936	Generic	Generic	false
2011	186	New Drug	New Drug	false
2012	1074	Generic	Generic	false
2012	183	New Drug	New Drug	false
2013	844	Generic	Generic	false
2013	184	New Drug	New Drug	false
2014	803	Generic	Generic	false
2014	185	New Drug	New Drug	false
2015	1087	Generic	Generic	false
2015	200	New Drug	New Drug	false
2016	1262	Generic	Generic	false
2016	174	New Drug	New Drug	false
2017	1602	Generic	Generic	false
2017	238	New Drug	New Drug	false
2018	1461	Generic	Generic	false
2018	221	New Drug	New Drug	false
2019	1641	Generic	Generic	false
2019	190	New Drug	New Drug	false
2020	1388	Generic	Generic	false
2020	187	New Drug	New Drug	false
2021	1289	Generic	Generic	false
2021	205	New Drug	New Drug	false
2022	1424	Generic	Generic	false
2022	190	New Drug	New Drug	false
2023	1655	Generic	Generic	false
2023	249	New Drug	New Drug	false
2024	1382	Generic	Generic	false
2024	210	New Drug	New Drug	false
2025	304	Generic	Generic	false
2025	23	New Drug	New Drug	false

Chart Configuration

{
  "grid": {
    "left": "3%",
    "right": "4%",
    "bottom": "3%",
    "containLabel": true
  },
  "title": {
    "text": "Comparison of Generic and New Drug Applications by Year"
  },
  "xAxis": {
    "data": [
      "1982",
      "1983",
      "1984",
      "1985",
      "1986",
      "1987",
      "1988",
      "1989",
      "1990",
      "1991",
      "1992",
      "1993",
      "1994",
      "1995",
      "1996",
      "1997",
      "1998",
      "1999",
      "2000",
      "2001",
      "2002",
      "2003",
      "2004",
      "2005",
      "2006",
      "2007",
      "2008",
      "2009",
      "2010",
      "2011",
      "2012",
      "2013",
      "2014",
      "2015",
      "2016",
      "2017",
      "2018",
      "2019",
      "2020",
      "2021",
      "2022",
      "2023",
      "2024",
      "2025"
    ],
    "name": "Year",
    "type": "category"
  },
  "yAxis": {
    "name": "Number of Applications",
    "type": "value"
  },
  "legend": {
    "data": [
      "Generic",
      "New Drug"
    ]
  },
  "series": [
    {
      "data": [
        3607,
        389,
        384,
        503,
        680,
        710,
        667,
        269,
        92,
        192,
        212,
        243,
        214,
        294,
        359,
        427,
        355,
        281,
        459,
        361,
        603,
        479,
        689,
        640,
        703,
        993,
        1012,
        968,
        858,
        936,
        1074,
        844,
        803,
        1087,
        1262,
        1602,
        1461,
        1641,
        1388,
        1289,
        1424,
        1655,
        1382,
        304
      ],
      "name": "Generic",
      "type": "bar"
    },
    {
      "data": [
        2898,
        149,
        266,
        193,
        172,
        131,
        260,
        159,
        113,
        115,
        179,
        137,
        122,
        166,
        243,
        233,
        198,
        148,
        192,
        120,
        206,
        174,
        190,
        136,
        189,
        162,
        158,
        176,
        152,
        186,
        183,
        184,
        185,
        200,
        174,
        238,
        221,
        190,
        187,
        205,
        190,
        249,
        210,
        23
      ],
      "name": "New Drug",
      "type": "bar"
    }
  ],
  "tooltip": {
    "trigger": "axis",
    "axisPointer": {
      "type": "shadow"
    }
  }
}