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
marketplaces · v1 · head of marketplaces
Status active, not suppressed or paused as of now, showing at least one unit in stock.
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
merchandising · v1 · head of merchandising
Sold at least one unit in the last selling_days days.
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