Show the top 10 companies and their most common...

Created on Apr 11, 2025

Prompt

Show the top 10 companies and their most common routes of administration

Chart

Query Results

SQL Query:

WITH CompanyRouteCounts AS (
    SELECT
        applicant_full_name,
        route,
        COUNT(*) AS route_count,
        ROW_NUMBER() OVER (PARTITION BY applicant_full_name ORDER BY COUNT(*) DESC) AS rn
    FROM
        orange_book_products
    GROUP BY
        applicant_full_name,
        route
),
Top10Companies AS (
    SELECT
        applicant_full_name
    FROM
        orange_book_products
    GROUP BY
        applicant_full_name
    ORDER BY
        COUNT(DISTINCT ingredient) DESC
    LIMIT 10
)
SELECT
    crc.applicant_full_name AS category,
    crc.route_count AS value,
    crc.route AS series,
    crc.applicant_full_name || ' - ' || crc.route AS label,
    CASE WHEN crc.rn = 1 THEN TRUE ELSE FALSE END AS highlight
FROM
    CompanyRouteCounts crc
JOIN
    Top10Companies tc ON crc.applicant_full_name = tc.applicant_full_name
WHERE crc.rn = 1
ORDER BY category;

Results:

category	value	series	label	highlight
APOTEX INC	279	TABLET;ORAL	APOTEX INC - TABLET;ORAL	true
AUROBINDO PHARMA LTD	555	TABLET;ORAL	AUROBINDO PHARMA LTD - TABLET;ORAL	true
CHARTWELL RX SCIENCES LLC	528	TABLET;ORAL	CHARTWELL RX SCIENCES LLC - TABLET;ORAL	true
HIKMA PHARMACEUTICALS USA INC	326	INJECTABLE;INJECTION	HIKMA PHARMACEUTICALS USA INC - INJECTABLE;INJECTION	true
HOSPIRA INC	666	INJECTABLE;INJECTION	HOSPIRA INC - INJECTABLE;INJECTION	true
NORVIUM BIOSCIENCE LLC	258	TABLET;ORAL	NORVIUM BIOSCIENCE LLC - TABLET;ORAL	true
SANDOZ INC	297	TABLET;ORAL	SANDOZ INC - TABLET;ORAL	true
TEVA PHARMACEUTICALS USA INC	408	TABLET;ORAL	TEVA PHARMACEUTICALS USA INC - TABLET;ORAL	true
WATSON LABORATORIES INC	749	TABLET;ORAL	WATSON LABORATORIES INC - TABLET;ORAL	true
ZYDUS PHARMACEUTICALS USA INC	357	TABLET;ORAL	ZYDUS PHARMACEUTICALS USA INC - TABLET;ORAL	true

Chart Configuration

{
  "grid": {
    "left": "3%",
    "right": "4%",
    "bottom": "3%",
    "containLabel": true
  },
  "title": {
    "top": "20px",
    "left": "center",
    "text": "Top 10 Companies and Their Most Common Routes of Administration"
  },
  "xAxis": {
    "data": [
      "APOTEX INC",
      "AUROBINDO PHARMA LTD",
      "CHARTWELL RX SCIENCES LLC",
      "HIKMA PHARMACEUTICALS USA INC",
      "HOSPIRA INC",
      "NORVIUM BIOSCIENCE LLC",
      "SANDOZ INC",
      "TEVA PHARMACEUTICALS USA INC",
      "WATSON LABORATORIES INC",
      "ZYDUS PHARMACEUTICALS USA INC"
    ],
    "name": "Frequency of Most Common Route",
    "type": "value",
    "axisLabel": {
      "margin": 10
    },
    "boundaryGap": [
      0,
      0.01
    ]
  },
  "yAxis": {
    "data": [],
    "name": "Company",
    "type": "category",
    "axisLabel": {
      "margin": 10
    }
  },
  "legend": {
    "data": [
      "INJECTABLE;INJECTION",
      "TABLET;ORAL"
    ],
    "show": false,
    "right": "10%"
  },
  "series": [
    {
      "data": [
        0,
        0,
        0,
        326,
        666,
        0,
        0,
        0,
        0,
        0
      ],
      "name": "INJECTABLE;INJECTION",
      "type": "bar"
    },
    {
      "data": [
        279,
        555,
        528,
        0,
        0,
        258,
        297,
        408,
        749,
        357
      ],
      "name": "TABLET;ORAL",
      "type": "bar"
    }
  ],
  "tooltip": {
    "trigger": "axis",
    "formatter": "{b}: {c}",
    "axisPointer": {
      "type": "shadow"
    }
  }
}