Summary: Warehouse Delivery Comparison Query 2023
Summary: Warehouse delivery comparison analysis 2023
File History Snapshot (2025-12-05T23:27:14.923Z)
USER
  hi!if I wanted tosee what thetop Warehouse was for deliveries in 2023, and then generates a tablewhich Compares allwarehousesagainst that one, showing a percentage difference in deliveries against the top one, what would that look like?
ASSISTANT
  Hi! Here's how that query would look:
  
  ```sql
  WITH warehouse_totals AS (
      SELECT 
          warehouse_id,
          warehouse_name,
          SUM(total_shipments) AS total_deliveries
      FROM daily_delivery_metrics
      WHERE YEAR(report_date) = 2023
      GROUP BY warehouse_id, warehouse_name
  ),
  top_warehouse AS (
      SELECT total_deliveries
      FROM warehouse_totals
      ORDER BY total_deliveries DESC
      LIMIT 1
  )
  SELECT 
      wt.warehouse_name,
      wt.total_deliveries,
      (SELECT total_deliveries FROM top_warehouse) AS top_deliveries,
      ROUND(
          (wt.total_deliveries - (SELECT total_deliveries FROM top_warehouse)) 
          / (SELECT total_deliveries FROM top_warehouse) * 100, 
          1
      ) AS pct_diff_from_top
  FROM warehouse_totals wt
  ORDER BY wt.total_deliveries DESC;
  ```
  
  This:
  1. Sums up deliveries per warehouse for 2023
  2. Finds the top warehouse's total
  3. Shows each warehouse with their count and percentage difference from the leader (the top one will show 0%, others will be negative)
  
  Want me to run it and show you the results?
  Tokens: in=2 out=321 cache=14929
File History Snapshot (2025-12-05T23:29:45.415Z)
USER
  sure :)
ASSISTANT
Tool: Bash
  (toolu_0192mVw3XX7iAPDJMqriGaHb)
    command: mariadb practice -e "
    WITH warehouse_totals AS (
        SELECT 
            warehouse_id,
            warehouse_name,
            SUM(total_shipments) AS total_deliveries
        FROM daily_delivery_metrics
        WHERE YE...
    description: Run warehouse comparison query
  Tokens: in=2 out=314 cache=24148
Result
  (toolu_0192mVw3XX7iAPDJMqriGaHb)
    warehouse_name	total_deliveries	top_deliveries	pct_diff_from_top
    Seattle Gateway	2832	2832	0.0
    Portland Terminal	2778	2832	-1.9
    San Diego Center	2775	2832	-2.0
    San Francisco Bay	2766	2832	-2.3
    Salt Lake Depot	2762	2832	-2.5
    Fresno Valley	2745	2832	-3.1
    Las Vegas Fulfillment	2741	2832	-3.2
    Denver Mountain	2726	2832	-3.7
    Phoenix Distribution	2716	2832	-4.1
    Los Angeles Hub	2683	2832	-5.3
ASSISTANT
  Here are the results:
  
  | Warehouse | Deliveries | % from Top |
  |-----------|-----------|------------|
  | **Seattle Gateway** | 2,832 | 0% (the leader!) |
  | Portland Terminal | 2,778 | -1.9% |
  | San Diego Center | 2,775 | -2.0% |
  | San Francisco Bay | 2,766 | -2.3% |
  | Salt Lake Depot | 2,762 | -2.5% |
  | Fresno Valley | 2,745 | -3.1% |
  | Las Vegas Fulfillment | 2,741 | -3.2% |
  | Denver Mountain | 2,726 | -3.7% |
  | Phoenix Distribution | 2,716 | -4.1% |
  | Los Angeles Hub | 2,683 | -5.3% |
  
  Seattle Gateway was the top warehouse in 2023. The warehouses are pretty close in performance—the biggest gap is only 5.3% between first and last place.
  
  Want me to save this query as a file you can download?
  Tokens: in=0 out=265 cache=24475
