Rails 24:00 Orvane Home & Outdoor

Ask

Questions are answered from the dictionary's own SQL, so a number here is the number the operator acts on. When a term has two meanings, both answers come back with how far they overlap.

How many active listings do we have?late shipments last 7 daysnet sales this weekchargebacks in the windowclean returns last 30 daysstock on hand

'Late shipment' has 2 definitions and no canonical one: marketplaces (head of marketplaces): 302 orders; customer_service (head of customer service): 459 orders. Which one do you mean?

302 orders

302 orders according to the head of marketplaces (marketplaces v1, last 7 days)

Shipped in the period, after its ship-by time plus grace_minutes. This is the number the marketplaces use for the late-shipment rate on the seller scorecard.

SQL
SELECT o.id AS order_id, o.order_no, m.name AS marketplace, o.ship_by, o.shipped_at,
       date_diff('minute', o.ship_by, o.shipped_at) AS minutes_late
FROM orders o JOIN marketplaces m ON m.id = o.marketplace_id
WHERE o.shipped_at >= TIMESTAMP '2026-11-21 00:00:00' AND o.shipped_at < TIMESTAMP '2026-11-28 00:00:00' AND o.shipped_at <= TIMESTAMP '2026-11-28 00:00:00'
  AND o.shipped_at > o.ship_by + INTERVAL (0) MINUTE
ORDER BY o.shipped_at

459 orders

459 orders according to the head of customer service (customer_service v1, last 7 days)

Delivered in the period after the promised date: the ship-by date plus the lane's normal transit days, at the end of that day. A parcel that left on time but sat in a carrier depot is late here and on time for the marketplace.

SQL
SELECT o.id AS order_id, o.order_no, m.name AS marketplace, o.ship_by, o.delivered_at,
       CAST(o.ship_by AS DATE) + ln.transit_days + 1 AS promised_by
FROM orders o JOIN marketplaces m ON m.id = o.marketplace_id JOIN lanes ln ON ln.id = o.lane_id
WHERE o.delivered_at >= TIMESTAMP '2026-11-21 00:00:00' AND o.delivered_at < TIMESTAMP '2026-11-28 00:00:00' AND o.delivered_at <= TIMESTAMP '2026-11-28 00:00:00'
  AND o.delivered_at >= CAST(CAST(o.ship_by AS DATE) + ln.transit_days + 1 AS TIMESTAMP)
ORDER BY o.delivered_at
Overlap, counted by order_id: only marketplaces 299, both 3, only customer service 456.