DB Olympics

Queries · q237 · Chief Operating Officer · week 2, night 2

Fulfillment shortfall and delivery speed by merchant vertical, 2025

“Do apparel/footwear merchants, where picking discrepancies are concentrated, actually see more orders go unfulfilled or delivered slowly -- or is that purely an inventory/accounting issue with no customer-facing effect?”

Manager · asked the questionPriya RamanChief Operating Officer. Came from a third-party logistics provider. Wants to know how reliable the delivery promise is that merchants make on Tidewick.
Analyst · planned the reportLena HartmannSkeptical: checks definitions and sanity totals before trusting a trend.
Engineer · wrote the SQLBeaWrites SQL the way the company's BI tool generates it: verbose fully qualified aliases (t0, t1, ...), a subquery per measure joined back on the grain keys, COALESCE everywhere.

Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told

  1. Priya Raman writes to Lena Hartmann

    The manager, at the start of week 2

    Subject: Carrier picture confirms it — now I want to know what we can actually fix

    Lena,

    This is exactly the clarity I was hoping for — "it's the carriers, not the warehouses" is a much more actionable story than "fulfillment is inconsistent." A few things jumped out, including from what Daniel's and Tomasz's teams posted this week, and I want to chase them down before we draft anything for merchants or carrier partners.

    What surprised me: Albion Parcel going from solid to broken in November and staying broken through December is different from Landfall and Lowlands, which sound like long-term weak performers we've just tolerated. One is a relationship we may need to end; the other looks like it could be an acute problem (capacity, a route, a depot) that might actually be fixable if we understood the trigger. I don't want to lump all three into one "bad carriers" conversation when they may need completely different responses.

    I also noticed Daniel's team's finding that "where's my order" tickets spike in lockstep with late deliveries each November/December — that's a direct line from the carrier problem to support cost and customer satisfaction, and I'd like to be able to say that explicitly in my own numbers rather than borrowing his.

    Questions for this week:

    1. Of the three weak carriers, which ones are tied to specific regions, lanes, or merchants, versus broadly weak everywhere? I want to know if Albion's November collapse was localized (a single hub, country, or set of merchants) or general — that tells us whether this is a "fix one bad link" problem or a "this carrier can't be trusted" problem.
    2. If we could bring our weak carriers up to the standard of our good ones, how much of our delivery unreliability would actually go away? I want a sense of the ceiling — is this 80% of our problem, or just the long tail?
    3. Can you give me a clean, non-censored view of whether we're genuinely behind on restocking from suppliers, or if that was a year-end timing artifact? You flagged this yourself — I'd like it resolved before I mention supplier reliability to anyone.
    4. Is there any sign that the picking discrepancies Tomasz's team flagged (concentrated in apparel/footwear) are contributing to delivery delays or failed promises — e.g., orders stalling or being corrected after the fact — or is that purely a shrinkage/accounting issue with no customer-facing effect? I want to know if it belongs in the same story as delivery reliability or is a separate track entirely.

    If it's a tight week, prioritize #1 and #2 — I want the carrier narrative airtight before I take it anywhere. Returns/restock cycle time can wait one more week if needed.

    Thanks, Priya

  2. Lena Hartmann plans the night

    The analyst · week 2, night 2, request 3 of 3

    Night 1 resolved priority #1 cleanly, and the answer is more nuanced than "weak carrier = weak carrier": (a) Albion Parcel's collapse is NOT localized -- it hit both countries it serves (UK and Ireland) and literally every origin warehouse/merchant it ships for, uniformly, the moment November started (pct-within-3-days fell from ~70-93% to ~0-40% everywhere at once, transit days jumping from ~2-2.5 to 8-17 days across the board). That rules out "one bad depot" or "one bad merchant" and points to a systemic capacity failure at Albion itself -- useful to know before the carrier conversation, but it's a harder story (a carrier that broke under load everywhere), not an easy single-fix. (b) Landfall Freight is uniformly bad across all 18 countries it serves (14-38% within 3 days everywhere, no bright spots) -- genuinely a "this carrier can't be trusted" case. (c) Lowlands Express is the interesting exception: it's actually solid on its home turf (Netherlands 79.9%, Belgium 69.3%, France 63.0% within 3 days -- 64.5% of its volume) but poor everywhere else (15-36% within 3 days on the remaining 35.5%). That's a lane-reassignment fix, not a carrier breakup. Tonight I'm covering priority #2 (the reliability ceiling, using a clean full-year carrier table so I can compute the weighted improvement by hand) and starting on #3 (a non-censored PO view, restricting to purchase orders old enough to have fully played out) and #4 (whether the apparel/footwear picking discrepancies Fatima's team flagged show up in delivery shortfalls, not just inventory accounting).

    Fulfillment shortfall and delivery speed by merchant vertical, 2025
    Question
    Do apparel/footwear merchants, where picking discrepancies are concentrated, actually see more orders go unfulfilled or delivered slowly -- or is that purely an inventory/accounting issue with no customer-facing effect?
    One row per
    merchant industry/vertical (tenants.industry), 2025
    Columns
    • industry
    • orders_placed_2025 (orders with placed_at in 2025 and a terminal status: fulfilled, cancelled, or refunded)
    • pct_orders_with_fulfillment_shortfall (share of those orders where SUM(order_items.quantity_fulfilled) < SUM(order_items.quantity) for the order)
    • orders_fully_delivered (subset of those orders where status='fulfilled' and every linked shipment, via fulfillments, has a non-null delivered_at)
    • avg_delivery_days (placed_at to max linked shipment delivered_at, fully-delivered subset only)
    • pct_within_5_days (fully-delivered subset only)
  3. Bea writes the SQL

    The engineer · 1 dry run on the empty schema, then the submission

    Dry run 1 · STATUS: COMPILES (dry run on the empty schema, no data read)
    WITH terminal_orders AS (
      SELECT o.id AS order_id, o.tenant_id, t.industry, o.status, o.placed_at
      FROM orders o
      JOIN tenants t ON t.id = o.tenant_id
      WHERE o.placed_at >= TIMESTAMP '2025-01-01' AND o.placed_at < TIMESTAMP '2026-01-01'
        AND o.status IN ('fulfilled','cancelled','refunded')
        AND t.industry IS NOT NULL
    ),
    order_qty AS (
      SELECT oi.order_id, SUM(oi.quantity) AS qty_ordered, SUM(oi.quantity_fulfilled) AS qty_fulfilled
      FROM order_items oi
      GROUP BY oi.order_id
    ),
    order_shipment AS (
      SELECT f.order_id,
             COUNT(s.id) AS shipment_count,
             SUM(CASE WHEN s.delivered_at IS NULL THEN 1 ELSE 0 END) AS undelivered_count,
             MAX(s.delivered_at) AS max_delivered_at
      FROM fulfillments f
      JOIN shipments s ON s.fulfillment_id = f.id
      GROUP BY f.order_id
    ),
    order_flags AS (
      SELECT
        t0.order_id,
        t0.tenant_id,
        t0.industry,
        t0.status,
        t0.placed_at,
        COALESCE(q.qty_ordered, 0) AS qty_ordered,
        COALESCE(q.qty_fulfilled, 0) AS qty_fulfilled,
        CASE WHEN COALESCE(q.qty_fulfilled,0) < COALESCE(q.qty_ordered,0) THEN 1 ELSE 0 END AS has_shortfall,
        CASE WHEN t0.status = 'fulfilled'
                  AND s.shipment_count IS NOT NULL
                  AND s.shipment_count > 0
                  AND COALESCE(s.undelivered_count,0) = 0
             THEN 1 ELSE 0 END AS is_fully_delivered,
        s.max_delivered_at
      FROM terminal_orders t0
      LEFT JOIN order_qty q ON q.order_id = t0.order_id
      LEFT JOIN order_shipment s ON s.order_id = t0.order_id
    )
    SELECT
      industry,
      COUNT(*) AS orders_placed_2025,
      ROUND(100.0 * SUM(has_shortfall) / NULLIF(COUNT(*),0), 1) AS pct_orders_with_fulfillment_shortfall,
      SUM(is_fully_delivered) AS orders_fully_delivered,
      ROUND(CAST(AVG(CASE WHEN is_fully_delivered=1 THEN EXTRACT(EPOCH FROM (max_delivered_at - placed_at))/86400.0 END) AS numeric), 2) AS avg_delivery_days,
      ROUND(100.0 * SUM(CASE WHEN is_fully_delivered=1 AND (max_delivered_at - placed_at) <= INTERVAL '5 days' THEN 1 ELSE 0 END) / NULLIF(SUM(is_fully_delivered),0), 1) AS pct_within_5_days
    FROM order_flags
    GROUP BY industry
    ORDER BY pct_orders_with_fulfillment_shortfall DESC, industry ASC

    “Query compiles and returns the expected columns. Submitting.”

    Submitted · the SQL of the last dry run, unchanged · Submitted to tonight's batch. Your work on this request is done.

    “Submitted. Summary of the approach: - Grain: one row per tenants.industry for 2025. - orders_placed_2025: orders with placed_at in 2025 and terminal status (fulfilled, cancelled, refunded). - pct_orders_with_fulfillment_shortfall: share of those orders where summed order_items.quantity_fulfilled fell short of summed quantity. - orders_fully_delivered / avg_delivery_days / pct_within_5_days: restricted to status='fulfilled' orders that have at least one linked shipment (via fulfillments) and no shipment with a null delivered_at; delivery time is placed_at to the latest linked delivered_at. Noted in the submit note: orders marked fulfilled but with zero linked shipment rows are excluded from the "fully delivered" subset (can't compute a delivery date for them), and tenants with a null industry are dropped since the report is organized by vertical.”

  4. The report comes back overnight

    As Lena Hartmann saw it, from the small dataset the agents worked on

    industry                  | orders_placed_2025 | pct_orders_with_fulfillment_shortfall | orders_fully_delivered | avg_delivery_days | pct_within_5_days
    --------------------------+--------------------+---------------------------------------+------------------------+-------------------+------------------
    Beauty                    |               1561 |                                   4.7 |                   1451 |              4.55 |              73.8
    Apparel & Footwear        |               7149 |                                   4.5 |                   6264 |              3.84 |              82.4
    Sports & Outdoor          |               1014 |                                   4.2 |                    916 |              4.05 |              77.8
    Home & Living             |               1658 |                                   4.0 |                   1519 |              4.43 |              73.7
    Jewelry & Accessories     |                 50 |                                   4.0 |                     46 |              4.23 |              73.9
    Consumer Electronics      |                575 |                                   3.7 |                    534 |              4.08 |              76.2
    Books & Stationery        |                159 |                                   3.1 |                    151 |              4.16 |              80.1
    Pet Supplies              |               1448 |                                   3.0 |                   1385 |              4.28 |              77.8
    Office Supplies (B2B)     |                526 |                                   1.9 |                    512 |              3.95 |              77.3
    Food & Coffee             |               7210 |                                   1.7 |                   6997 |              3.93 |              80.5
    Industrial Supplies (B2B) |                773 |                                   1.6 |                    758 |              4.26 |              72.2
    (11 rows)
  5. What came of it

    The memo and the queries that followed; links found by title, so inferred

    It builds on:

  6. It becomes benchmark query q237

    Changed only where the benchmark needs the same result on every system

    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision; bare NUMERIC cast that only served the rounding removed
    • numericsdocs/NUMERICS.md: EXTRACT(EPOCH FROM (b - a)) cast to NUMERIC(18,6)
    • made explicita selected t.col gets AS col, its standard name made explicit (12×)
    • placeholderseconds between
    • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (2×)
    The benchmark text, against the submitted SQL
    WITH terminal_orders AS (  SELECT o.id AS order_id, o.tenant_id, t.industry, o.status, o.placed_at  SELECT o.id AS order_id, o.tenant_id AS tenant_id, t.industry AS industry, o.status AS status, o.placed_at AS placed_at  FROM orders o  JOIN tenants t ON t.id = o.tenant_id  WHERE o.placed_at >= TIMESTAMP '2025-01-01' AND o.placed_at < TIMESTAMP '2026-01-01'    AND o.status IN ('fulfilled','cancelled','refunded')    AND t.industry IS NOT NULL),order_qty AS (  SELECT oi.order_id, SUM(oi.quantity) AS qty_ordered, SUM(oi.quantity_fulfilled) AS qty_fulfilled  SELECT oi.order_id AS order_id, SUM(oi.quantity) AS qty_ordered, SUM(oi.quantity_fulfilled) AS qty_fulfilled  FROM order_items oi  GROUP BY oi.order_id),order_shipment AS (  SELECT f.order_id,  SELECT f.order_id AS order_id,         COUNT(s.id) AS shipment_count,         SUM(CASE WHEN s.delivered_at IS NULL THEN 1 ELSE 0 END) AS undelivered_count,         MAX(s.delivered_at) AS max_delivered_at  FROM fulfillments f  JOIN shipments s ON s.fulfillment_id = f.id  GROUP BY f.order_id),order_flags AS (  SELECT    t0.order_id,    t0.tenant_id,    t0.industry,    t0.status,    t0.placed_at,    t0.order_id AS order_id,    t0.tenant_id AS tenant_id,    t0.industry AS industry,    t0.status AS status,    t0.placed_at AS placed_at,    COALESCE(q.qty_ordered, 0) AS qty_ordered,    COALESCE(q.qty_fulfilled, 0) AS qty_fulfilled,    CASE WHEN COALESCE(q.qty_fulfilled,0) < COALESCE(q.qty_ordered,0) THEN 1 ELSE 0 END AS has_shortfall,    CASE WHEN t0.status = 'fulfilled'              AND s.shipment_count IS NOT NULL              AND s.shipment_count > 0              AND COALESCE(s.undelivered_count,0) = 0         THEN 1 ELSE 0 END AS is_fully_delivered,    s.max_delivered_at    s.max_delivered_at AS max_delivered_at  FROM terminal_orders t0  LEFT JOIN order_qty q ON q.order_id = t0.order_id  LEFT JOIN order_shipment s ON s.order_id = t0.order_id)SELECT  industry,  COUNT(*) AS orders_placed_2025,  ROUND(100.0 * SUM(has_shortfall) / NULLIF(COUNT(*),0), 1) AS pct_orders_with_fulfillment_shortfall,  100.0 * SUM(has_shortfall) / NULLIF(COUNT(*),0) AS pct_orders_with_fulfillment_shortfall,  SUM(is_fully_delivered) AS orders_fully_delivered,  ROUND(CAST(AVG(CASE WHEN is_fully_delivered=1 THEN EXTRACT(EPOCH FROM (max_delivered_at - placed_at))/86400.0 END) AS numeric), 2) AS avg_delivery_days,  ROUND(100.0 * SUM(CASE WHEN is_fully_delivered=1 AND (max_delivered_at - placed_at) <= INTERVAL '5 days' THEN 1 ELSE 0 END) / NULLIF(SUM(is_fully_delivered),0), 1) AS pct_within_5_days  AVG(CASE WHEN is_fully_delivered=1 THEN {{seconds_between(placed_at, max_delivered_at)}}/86400.0 END) AS avg_delivery_days,  100.0 * SUM(CASE WHEN is_fully_delivered=1 AND (max_delivered_at - placed_at) <= INTERVAL '5 days' THEN 1 ELSE 0 END) / NULLIF(SUM(is_fully_delivered),0) AS pct_within_5_daysFROM order_flagsGROUP BY industryORDER BY pct_orders_with_fulfillment_shortfall DESC, industry ASCORDER BY pct_orders_with_fulfillment_shortfall DESC NULLS FIRST, industry ASC NULLS LAST
    WITH terminal_orders AS (
      SELECT o.id AS order_id, o.tenant_id AS tenant_id, t.industry AS industry, o.status AS status, o.placed_at AS placed_at
      FROM orders o
      JOIN tenants t ON t.id = o.tenant_id
      WHERE o.placed_at >= TIMESTAMP '2025-01-01' AND o.placed_at < TIMESTAMP '2026-01-01'
        AND o.status IN ('fulfilled','cancelled','refunded')
        AND t.industry IS NOT NULL
    ),
    order_qty AS (
      SELECT oi.order_id AS order_id, SUM(oi.quantity) AS qty_ordered, SUM(oi.quantity_fulfilled) AS qty_fulfilled
      FROM order_items oi
      GROUP BY oi.order_id
    ),
    order_shipment AS (
      SELECT f.order_id AS order_id,
             COUNT(s.id) AS shipment_count,
             SUM(CASE WHEN s.delivered_at IS NULL THEN 1 ELSE 0 END) AS undelivered_count,
             MAX(s.delivered_at) AS max_delivered_at
      FROM fulfillments f
      JOIN shipments s ON s.fulfillment_id = f.id
      GROUP BY f.order_id
    ),
    order_flags AS (
      SELECT
        t0.order_id AS order_id,
        t0.tenant_id AS tenant_id,
        t0.industry AS industry,
        t0.status AS status,
        t0.placed_at AS placed_at,
        COALESCE(q.qty_ordered, 0) AS qty_ordered,
        COALESCE(q.qty_fulfilled, 0) AS qty_fulfilled,
        CASE WHEN COALESCE(q.qty_fulfilled,0) < COALESCE(q.qty_ordered,0) THEN 1 ELSE 0 END AS has_shortfall,
        CASE WHEN t0.status = 'fulfilled'
                  AND s.shipment_count IS NOT NULL
                  AND s.shipment_count > 0
                  AND COALESCE(s.undelivered_count,0) = 0
             THEN 1 ELSE 0 END AS is_fully_delivered,
        s.max_delivered_at AS max_delivered_at
      FROM terminal_orders t0
      LEFT JOIN order_qty q ON q.order_id = t0.order_id
      LEFT JOIN order_shipment s ON s.order_id = t0.order_id
    )
    SELECT
      industry,
      COUNT(*) AS orders_placed_2025,
      100.0 * SUM(has_shortfall) / NULLIF(COUNT(*),0) AS pct_orders_with_fulfillment_shortfall,
      SUM(is_fully_delivered) AS orders_fully_delivered,
      AVG(CASE WHEN is_fully_delivered=1 THEN CAST(EXTRACT(EPOCH FROM (max_delivered_at - placed_at)) AS NUMERIC(18,6))/86400.0 END) AS avg_delivery_days,
      100.0 * SUM(CASE WHEN is_fully_delivered=1 AND (max_delivered_at - placed_at) <= INTERVAL '5 days' THEN 1 ELSE 0 END) / NULLIF(SUM(is_fully_delivered),0) AS pct_within_5_days
    FROM order_flags
    GROUP BY industry
    ORDER BY pct_orders_with_fulfillment_shortfall DESC NULLS FIRST, industry ASC NULLS LAST
    100 ms1 s10 s30 sHyper1.03 sDuckDB1.34 sGizmoSQL1.04 sclickhouse_brew1.89 sFirebolt916 msDataFusiontimeout (30 s)ClickHouseerror
    • DataFusionno result within 30 s
    • ClickHouseCode: 43. DB::Exception: No operation lessOrEquals between Decimal(18, 6) and IntervalDay: while executing 'FUNCTION lessOrEquals(minus(__table1.max_delivered_at, __table1.placed_at) :: 10, _CAST(5_IntervalDay, 'IntervalDay'_String) :: 17) -> lessOrEquals(minus(__table1.max_delivered_at, __table1.pl
  7. Everything, as recorded

    The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted

    Bea's whole session, 7 turns
    • The request, as Bea received it
      Report request from Lena Hartmann (analyst for the Chief Operating Officer):
      
      Title: Fulfillment shortfall and delivery speed by merchant vertical, 2025
      Question: Do apparel/footwear merchants, where picking discrepancies are concentrated, actually see more orders go unfulfilled or delivered slowly -- or is that purely an inventory/accounting issue with no customer-facing effect?
      Grain: one row per merchant industry/vertical (tenants.industry), 2025
      Columns:
      - industry
      - orders_placed_2025 (orders with placed_at in 2025 and a terminal status: fulfilled, cancelled, or refunded)
      - pct_orders_with_fulfillment_shortfall (share of those orders where SUM(order_items.quantity_fulfilled) < SUM(order_items.quantity) for the order)
      - orders_fully_delivered (subset of those orders where status='fulfilled' and every linked shipment, via fulfillments, has a non-null delivered_at)
      - avg_delivery_days (placed_at to max linked shipment delivered_at, fully-delivered subset only)
      - pct_within_5_days (fully-delivered subset only)
      Details: Tables: orders, order_items, fulfillments, shipments, tenants (industry). Scope: orders.placed_at between 2025-01-01 and 2025-12-31, status in ('fulfilled','cancelled','refunded') for the shortfall calculation (terminal orders only, so the shortfall flag is a final-state fact, not an in-progress one). Group by tenants.industry. Round pct columns to 1 decimal, avg_delivery_days to 2 decimals. Order by pct_orders_with_fulfillment_shortfall descending, tie-break by industry ascending. Expect under 12 rows (one per distinct industry value).
      
      Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Bea calls dry_run
      WITH terminal_orders AS (
        SELECT o.id AS order_id, o.tenant_id, t.industry, o.status, o.placed_at
        FROM orders o
        JOIN tenants t ON t.id = o.tenant_id
        WHERE o.placed_at >= TIMESTAMP '2025-01-01' AND o.placed_at < TIMESTAMP '2026-01-01'
          AND o.status IN ('fulfilled','cancelled','refunded')
          AND t.industry IS NOT NULL
      ),
      order_qty AS (
        SELECT oi.order_id, SUM(oi.quantity) AS qty_ordered, SUM(oi.quantity_fulfilled) AS qty_fulfilled
        FROM order_items oi
        GROUP BY oi.order_id
      ),
      order_shipment AS (
        SELECT f.order_id,
               COUNT(s.id) AS shipment_count,
               SUM(CASE WHEN s.delivered_at IS NULL THEN 1 ELSE 0 END) AS undelivered_count,
               MAX(s.delivered_at) AS max_delivered_at
        FROM fulfillments f
        JOIN shipments s ON s.fulfillment_id = f.id
        GROUP BY f.order_id
      ),
      order_flags AS (
        SELECT
          t0.order_id,
          t0.tenant_id,
          t0.industry,
          t0.status,
          t0.placed_at,
          COALESCE(q.qty_ordered, 0) AS qty_ordered,
          COALESCE(q.qty_fulfilled, 0) AS qty_fulfilled,
          CASE WHEN COALESCE(q.qty_fulfilled,0) < COALESCE(q.qty_ordered,0) THEN 1 ELSE 0 END AS has_shortfall,
          CASE WHEN t0.status = 'fulfilled'
                    AND s.shipment_count IS NOT NULL
                    AND s.shipment_count > 0
                    AND COALESCE(s.undelivered_count,0) = 0
               THEN 1 ELSE 0 END AS is_fully_delivered,
          s.max_delivered_at
        FROM terminal_orders t0
        LEFT JOIN order_qty q ON q.order_id = t0.order_id
        LEFT JOIN order_shipment s ON s.order_id = t0.order_id
      )
      SELECT
        industry,
        COUNT(*) AS orders_placed_2025,
        ROUND(100.0 * SUM(has_shortfall) / NULLIF(COUNT(*),0), 1) AS pct_orders_with_fulfillment_shortfall,
        SUM(is_fully_delivered) AS orders_fully_delivered,
        ROUND(CAST(AVG(CASE WHEN is_fully_delivered=1 THEN EXTRACT(EPOCH FROM (max_delivered_at - placed_at))/86400.0 END) AS numeric), 2) AS avg_delivery_days,
        ROUND(100.0 * SUM(CASE WHEN is_fully_delivered=1 AND (max_delivered_at - placed_at) <= INTERVAL '5 days' THEN 1 ELSE 0 END) / NULLIF(SUM(is_fully_delivered),0), 1) AS pct_within_5_days
      FROM order_flags
      GROUP BY industry
      ORDER BY pct_orders_with_fulfillment_shortfall DESC, industry ASC
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
      COLUMNS: industry, orders_placed_2025, pct_orders_with_fulfillment_shortfall, orders_fully_delivered, avg_delivery_days, pct_within_5_days
    • BeaQuery compiles and returns the expected columns. Submitting.
    • Bea calls submit
      WITH terminal_orders AS (
        SELECT o.id AS order_id, o.tenant_id, t.industry, o.status, o.placed_at
        FROM orders o
        JOIN tenants t ON t.id = o.tenant_id
        WHERE o.placed_at >= TIMESTAMP '2025-01-01' AND o.placed_at < TIMESTAMP '2026-01-01'
          AND o.status IN ('fulfilled','cancelled','refunded')
          AND t.industry IS NOT NULL
      ),
      order_qty AS (
        SELECT oi.order_id, SUM(oi.quantity) AS qty_ordered, SUM(oi.quantity_fulfilled) AS qty_fulfilled
        FROM order_items oi
        GROUP BY oi.order_id
      ),
      order_shipment AS (
        SELECT f.order_id,
               COUNT(s.id) AS shipment_count,
               SUM(CASE WHEN s.delivered_at IS NULL THEN 1 ELSE 0 END) AS undelivered_count,
               MAX(s.delivered_at) AS max_delivered_at
        FROM fulfillments f
        JOIN shipments s ON s.fulfillment_id = f.id
        GROUP BY f.order_id
      ),
      order_flags AS (
        SELECT
          t0.order_id,
          t0.tenant_id,
          t0.industry,
          t0.status,
          t0.placed_at,
          COALESCE(q.qty_ordered, 0) AS qty_ordered,
          COALESCE(q.qty_fulfilled, 0) AS qty_fulfilled,
          CASE WHEN COALESCE(q.qty_fulfilled,0) < COALESCE(q.qty_ordered,0) THEN 1 ELSE 0 END AS has_shortfall,
          CASE WHEN t0.status = 'fulfilled'
                    AND s.shipment_count IS NOT NULL
                    AND s.shipment_count > 0
                    AND COALESCE(s.undelivered_count,0) = 0
               THEN 1 ELSE 0 END AS is_fully_delivered,
          s.max_delivered_at
        FROM terminal_orders t0
        LEFT JOIN order_qty q ON q.order_id = t0.order_id
        LEFT JOIN order_shipment s ON s.order_id = t0.order_id
      )
      SELECT
        industry,
        COUNT(*) AS orders_placed_2025,
        ROUND(100.0 * SUM(has_shortfall) / NULLIF(COUNT(*),0), 1) AS pct_orders_with_fulfillment_shortfall,
        SUM(is_fully_delivered) AS orders_fully_delivered,
        ROUND(CAST(AVG(CASE WHEN is_fully_delivered=1 THEN EXTRACT(EPOCH FROM (max_delivered_at - placed_at))/86400.0 END) AS numeric), 2) AS avg_delivery_days,
        ROUND(100.0 * SUM(CASE WHEN is_fully_delivered=1 AND (max_delivered_at - placed_at) <= INTERVAL '5 days' THEN 1 ELSE 0 END) / NULLIF(SUM(is_fully_delivered),0), 1) AS pct_within_5_days
      FROM order_flags
      GROUP BY industry
      ORDER BY pct_orders_with_fulfillment_shortfall DESC, industry ASC
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • BeaSubmitted. Summary of the approach: - **Grain:** one row per `tenants.industry` for 2025. - **orders_placed_2025:** orders with `placed_at` in 2025 and terminal status (`fulfilled`, `cancelled`, `refunded`). - **pct_orders_with_fulfillment_shortfall:** share of those orders where summed `order_items.quantity_fulfilled` fell short of summed `quantity`. - **orders_fully_delivered / avg_delivery_days / pct_within_5_days:** restricted to `status='fulfilled'` orders that have at least one linked shipment (via `fulfillments`) and no shipment with a null `delivered_at`; delivery time is `placed_at` to the latest linked `delivered_at`. Noted in the submit note: orders marked `fulfilled` but with zero linked shipment rows are excluded from the "fully delivered" subset (can't compute a delivery date for them), and tenants with a null industry are dropped since the report is organized by vertical.