Compare generic and new drug applications by year

Created on Apr 7, 2025

Prompt

Chart

Query Results

SQL Query:

SELECT
    EXTRACT(YEAR FROM approval_date) AS approval_year,
    SUM(CASE WHEN appl_type = 'A' THEN 1 ELSE 0 END) AS generic_applications,
    SUM(CASE WHEN appl_type = 'N' THEN 1 ELSE 0 END) AS new_applications,
    COUNT(*) AS total_applications
FROM
    orange_book_products
WHERE approval_date IS NOT NULL
GROUP BY
    approval_year
ORDER BY
    approval_year;

Results:

approval_year	generic_applications	new_applications	total_applications
1982	3607	2898	6505
1983	389	149	538
1984	384	266	650
1985	503	193	696
1986	680	172	852
1987	710	131	841
1988	667	260	927
1989	269	159	428
1990	92	113	205
1991	192	115	307
1992	212	179	391
1993	243	137	380
1994	214	122	336
1995	294	166	460
1996	359	243	602
1997	427	233	660
1998	355	198	553
1999	281	148	429
2000	459	192	651
2001	361	120	481
2002	603	206	809
2003	479	174	653
2004	689	190	879
2005	640	136	776
2006	703	189	892
2007	993	162	1155
2008	1012	158	1170
2009	968	176	1144
2010	858	152	1010
2011	936	186	1122
2012	1074	183	1257
2013	844	184	1028
2014	803	185	988
2015	1087	200	1287
2016	1262	174	1436
2017	1602	238	1840
2018	1461	221	1682
2019	1641	190	1831
2020	1388	187	1575
2021	1289	205	1494
2022	1424	190	1614
2023	1655	249	1904
2024	1382	210	1592
2025	304	23	327

Chart Configuration

{
  "grid": {
    "left": "3%",
    "right": "4%",
    "bottom": "10%",
    "containLabel": true
  },
  "title": {
    "left": "center",
    "text": "Generic vs. 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",
    "axisLabel": {
      "rotate": 45,
      "interval": 0
    }
  },
  "yAxis": {
    "name": "Number of Applications",
    "type": "value"
  },
  "legend": {
    "top": "bottom",
    "data": [
      "Generic Applications",
      "New Applications",
      "Total Applications"
    ]
  },
  "series": [
    {
      "id": "generic_applications",
      "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 Applications",
      "type": "bar",
      "color": "#5470c6",
      "label": {
        "show": true,
        "position": "inside"
      },
      "stack": "applications",
      "emphasis": {
        "focus": "series"
      }
    },
    {
      "id": "new_applications",
      "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 Applications",
      "type": "bar",
      "color": "#91cc75",
      "label": {
        "show": true,
        "position": "inside"
      },
      "stack": "applications",
      "emphasis": {
        "focus": "series"
      }
    },
    {
      "id": "total_applications",
      "data": [
        "6505",
        "538",
        "650",
        "696",
        "852",
        "841",
        "927",
        "428",
        "205",
        "307",
        "391",
        "380",
        "336",
        "460",
        "602",
        "660",
        "553",
        "429",
        "651",
        "481",
        "809",
        "653",
        "879",
        "776",
        "892",
        "1155",
        "1170",
        "1144",
        "1010",
        "1122",
        "1257",
        "1028",
        "988",
        "1287",
        "1436",
        "1840",
        "1682",
        "1831",
        "1575",
        "1494",
        "1614",
        "1904",
        "1592",
        "327"
      ],
      "name": "Total Applications",
      "type": "line",
      "color": "#fac858",
      "smooth": true,
      "yAxisIndex": 0
    }
  ],
  "tooltip": {
    "trigger": "axis",
    "formatter": "Year: {b}<br />{a0}: {c0}<br />{a1}: {c1}<br />Total: {c2}",
    "axisPointer": {
      "type": "shadow"
    }
  }
}