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

'Clean return' has 2 definitions and no canonical one: warehouse (head of fulfilment): 10,263 returns; trust (trust and safety lead): 7,790 returns. Which one do you mean?

10,263 returns

10,263 returns according to the head of fulfilment (warehouse v1, last 30 days)

Received in the period in resaleable condition.

SQL
SELECT r.id AS return_id, r.order_id, r.customer_id, r.reason, r.condition, r.refund_amount
FROM returns r
WHERE r.received_at >= TIMESTAMP '2026-10-29 00:00:00' AND r.received_at < TIMESTAMP '2026-11-28 00:00:00' AND r.received_at <= TIMESTAMP '2026-11-28 00:00:00'
  AND r.condition = 'resaleable'

7,790 returns

7,790 returns according to the trust and safety lead (trust v1, last 30 days)

Received in the period in resaleable condition, for a fit or preference reason (not a claim that something was missing or wrong), from an account whose returns are under abuse_line of its delivered orders.

SQL
WITH rate AS (
  SELECT o.customer_id, count(DISTINCT r.id) / greatest(count(DISTINCT o.id), 1) AS return_rate
  FROM orders o LEFT JOIN returns r ON r.order_id = o.id AND r.received_at <= TIMESTAMP '2026-11-28 00:00:00'
  WHERE o.delivered_at <= TIMESTAMP '2026-11-28 00:00:00' AND o.delivered_at > TIMESTAMP '2026-11-28 00:00:00' - INTERVAL 90 DAY
  GROUP BY o.customer_id
)
SELECT r.id AS return_id, r.order_id, r.customer_id, r.reason, r.condition, r.refund_amount
FROM returns r JOIN rate t ON t.customer_id = r.customer_id
WHERE r.received_at >= TIMESTAMP '2026-10-29 00:00:00' AND r.received_at < TIMESTAMP '2026-11-28 00:00:00' AND r.received_at <= TIMESTAMP '2026-11-28 00:00:00'
  AND r.condition = 'resaleable' AND r.reason IN ('doesnt_fit', 'changed_mind', 'arrived_late', 'not_as_pictured')
  AND t.return_rate < 0.6
Overlap, counted by return_id: only warehouse 2,473, both 7,790, only trust 0.