Rails 24:00 Orvane Home & Outdoor

← Definitions

Active listing active_listing

A listing that is "active". To the marketplaces team it is live and buyable now; to merchandising it is one that actually sells. Two definitions, no canonical one.

Parameters: selling_days = 30

Run the tests on this world

marketplaces · v1 · head of marketplaces

Status active, not suppressed or paused as of now, showing at least one unit in stock.

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

WITH state AS (
  SELECT listing_id, arg_max(type, "at") AS state FROM listing_events
  WHERE type IN ('suppressed', 'reinstated', 'resubmitted', 'paused', 'resumed') AND "at" <= {now}
  GROUP BY listing_id
)
SELECT l.id AS listing_id, l.marketplace_id, l.product_id, l.qty_shown
FROM listings l LEFT JOIN state s ON s.listing_id = l.id
WHERE l.status <> 'inactive' AND coalesce(s.state, 'reinstated') NOT IN ('suppressed', 'paused')
  AND l.qty_shown > 0

Tests: count_between

merchandising · v1 · head of merchandising

Sold at least one unit in the last selling_days days.

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

SELECT ol.listing_id, any_value(o.marketplace_id) AS marketplace_id, sum(ol.qty) AS units
FROM order_lines ol JOIN orders o ON o.id = ol.order_id
WHERE o.placed_at <= {now} AND o.placed_at > {now} - INTERVAL ({selling_days}) DAY
  AND (o.cancelled_at IS NULL OR o.cancelled_at > {now})
GROUP BY ol.listing_id

Tests: count_between