SELECT
  date,
  AVG(rate) AS average_rate
FROM (
  SELECT
    DATE(t.shipdate) AS date,
    t_filtered.orderid,
    COUNT(DISTINCT t.trackingnumber) AS shipment_count,
    (
      SELECT COUNT(DISTINCT tn.trackingnumber)
      FROM trackingnumbers tn
      WHERE tn.orderid = t_filtered.orderid
    ) / COUNT(DISTINCT t.trackingnumber) AS rate
  FROM
    trackingnumbers t
    JOIN orders o ON t.orderid = o.orderid
    JOIN (
      SELECT orderid, MAX(shipdate) AS max_shipdate
      FROM trackingnumbers
      GROUP BY orderid
    ) t_filtered ON t.orderid = t_filtered.orderid AND t.shipdate = t_filtered.max_shipdate
    JOIN (
      SELECT orderid, COUNT(DISTINCT orderdetailid) AS orderdetail_count
      FROM orderdetails
      GROUP BY orderid
      HAVING orderdetail_count > 1
    ) od_counts ON t_filtered.orderid = od_counts.orderid
  WHERE
    o.orderstatus = 'Shipped' AND YEAR(t.shipdate) = 2024
  GROUP BY
    DATE(t.shipdate), t_filtered.orderid
) AS subquery
GROUP BY
  date