Rails 24:00 Orvane Home & Outdoor

← Definitions

Clean return clean_return

A return that is "clean". The warehouse means it can go back on the shelf; trust and safety means nothing about it points at abuse. Two definitions, no canonical one.

Parameters: abuse_line = 0.6

Test results

meaningtestpassedobservedexpected
warehousecount_betweenpassed10263between 100.0 and 20000.0
trustcount_betweenpassed7790between 100.0 and 20000.0

warehouse · v1 · head of fulfilment

Received in the period in resaleable condition.

Changed 2026-10-12: Agreed with the owner.

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 >= {period_start} AND r.received_at < {period_end} AND r.received_at <= {now}
  AND r.condition = 'resaleable'

Tests: count_between

trust · v1 · trust and safety lead

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.

Changed 2026-10-12: Agreed with the owner.

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 <= {now}
  WHERE o.delivered_at <= {now} AND o.delivered_at > {now} - 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 >= {period_start} AND r.received_at < {period_end} AND r.received_at <= {now}
  AND r.condition = 'resaleable' AND r.reason IN ('doesnt_fit', 'changed_mind', 'arrived_late', 'not_as_pictured')
  AND t.return_rate < {abuse_line}

Tests: count_between