Show the distribution of exclusivity durations

Created on Apr 10, 2025

Prompt

Chart

Query Results

SQL Query:

SELECT
    CASE
        WHEN (exclusivity_date::date - p.approval_date::date) <= 365 THEN 'Less than or equal to 1 year'
        WHEN (exclusivity_date::date - p.approval_date::date) > 365 AND (exclusivity_date::date - p.approval_date::date) <= (365 * 2) THEN '1 to 2 years'
        WHEN (exclusivity_date::date - p.approval_date::date) > (365 * 2) AND (exclusivity_date::date - p.approval_date::date) <= (365 * 3) THEN '2 to 3 years'
        WHEN (exclusivity_date::date - p.approval_date::date) > (365 * 3) AND (exclusivity_date::date - p.approval_date::date) <= (365 * 4) THEN '3 to 4 years'
        ELSE 'More than 4 years'
    END AS category,
    COUNT(*) AS value,
    'Exclusivity Duration' AS series,
    NULL AS label,
    FALSE AS highlight
FROM
    orange_book_exclusivities e
JOIN
    orange_book_products p ON e.appl_no = p.appl_no AND e.product_no = p.product_no
GROUP BY
    category
ORDER BY
    category;

Results:

category	value	series	label	highlight
1 to 2 years	10	Exclusivity Duration	null	false
2 to 3 years	36	Exclusivity Duration	null	false
3 to 4 years	187	Exclusivity Duration	null	false
Less than or equal to 1 year	34	Exclusivity Duration	null	false
More than 4 years	1795	Exclusivity Duration	null	false

Chart Configuration

{
  "grid": {
    "left": "3%",
    "right": "4%",
    "bottom": "3%",
    "containLabel": true
  },
  "title": {
    "top": "20px",
    "left": "center",
    "text": "Distribution of Exclusivity Durations"
  },
  "xAxis": [
    {
      "data": [],
      "name": "Exclusivity Duration (Years)",
      "type": "category",
      "axisLabel": {
        "rotate": 45,
        "interval": 0
      }
    }
  ],
  "yAxis": [
    {
      "name": "Number of Exclusivities",
      "type": "value",
      "axisLabel": {
        "formatter": "{value}"
      }
    }
  ],
  "legend": {
    "data": [
      "Exclusivity Duration"
    ]
  },
  "series": [
    {
      "data": [
        10,
        36,
        187,
        34,
        1795
      ],
      "name": "Exclusivity Duration",
      "type": "bar"
    }
  ],
  "tooltip": {
    "trigger": "axis",
    "formatter": "{b}: {c}",
    "axisPointer": {
      "type": "shadow"
    }
  }
}