What is shipping cost per order?

WITH label_cost AS (
  -- Billed cost wins: carriers reweigh for dimensional weight and post
  -- adjustments days after purchase. Fall back to quoted only while
  -- an adjustment is still pending, and count how often that happens.
  SELECT
    order_id,
    SUM(COALESCE(billed_cost, quoted_cost)) AS shipping_cost,
    COUNT(*) AS packages,
    BOOL_OR(billed_cost IS NULL) AS awaiting_adjustment
  FROM shipping_labels
  WHERE purchased_at >= CURRENT_DATE - INTERVAL '180 days'
    AND status NOT IN ('voided', 'refunded')
  GROUP BY 1
), per_order AS (
  -- Aggregate to order grain BEFORE averaging, or multi-package
  -- orders get counted once per label and pull the mean down.
  SELECT
    date_trunc('week', o.ordered_at) AS week,
    l.order_id,
    l.shipping_cost,
    l.packages,
    l.awaiting_adjustment,
    o.order_total
  FROM label_cost l
  JOIN creator_orders o USING (order_id)
)
SELECT
  week,
  COUNT(*) AS orders_shipped,
  ROUND(AVG(shipping_cost), 2) AS avg_cost_per_order,
  ROUND(
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY shipping_cost)::numeric, 2
  ) AS median_cost_per_order,
  ROUND(
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY shipping_cost)::numeric, 2
  ) AS p95_cost_per_order,
  -- Ratio of sums, not average of ratios: a $9 label on a $20 order
  -- would otherwise weigh as much as one on a $900 order.
  ROUND(
    100.0 * SUM(shipping_cost) / NULLIF(SUM(order_total), 0), 1
  ) AS cost_pct_of_order_value,
  ROUND(AVG(packages), 2) AS avg_packages,
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE awaiting_adjustment)
    / NULLIF(COUNT(*), 0), 1
  ) AS pct_orders_not_yet_adjusted
FROM per_order
GROUP BY 1
ORDER BY 1;