Suppressed listing listing_suppressed
An active listing that a marketplace has taken down (suppressed) for a content or catalogue reason, and that has stayed down long enough to cost sales.
Parameters: min_minutes = 30 min_units_7d = 5
marketplaces · v1 · head of marketplaces
The listing's latest status event (as of now) is a suppression, at least min_minutes old, and the listing sold at least min_units_7d units in the seven days before it was suppressed. A resubmission after a fix counts as handled while the marketplace reviews it. Automated content checks often lift within the hour, so a short suppression is not listed.
WITH state AS (
SELECT listing_id, arg_max(type, "at") AS state, max("at") AS state_at, arg_max(payload, "at") AS payload
FROM listing_events
WHERE type IN ('suppressed', 'reinstated', 'resubmitted') AND "at" <= {now}
GROUP BY listing_id
),
down AS (
SELECT * FROM state WHERE state = 'suppressed' AND state_at <= {now} - INTERVAL ({min_minutes}) MINUTE
),
lines AS (
SELECT ol.listing_id, ol.order_id, ol.qty, ol.unit_price FROM order_lines ol
WHERE ol.listing_id IN (SELECT listing_id FROM down)
),
sold AS (
SELECT d.listing_id, sum(li.qty) AS units, round(sum(li.qty * li.unit_price), 2) AS revenue
FROM lines li
JOIN down d ON d.listing_id = li.listing_id
JOIN orders o ON o.id = li.order_id
WHERE o.placed_at < d.state_at AND o.placed_at >= d.state_at - INTERVAL 7 DAY
GROUP BY d.listing_id
)
SELECT 'listing' AS subject_type, l.id AS subject_id, l.id AS listing_id, l.product_id, l.marketplace_id,
'listing:' || l.id AS scope, d.state_at AS started_at,
CASE WHEN s.units >= 20 THEN 'high' ELSE 'medium' END AS severity,
json_object(
'sku', p.sku, 'product', p.name, 'marketplace', m.name,
'reason', json_extract_string(d.payload, '$.reason'),
'minutes_suppressed', date_diff('minute', d.state_at, {now}),
'units_7d', s.units, 'revenue_7d', s.revenue,
'catalogue_gtin', p.gtin, 'category_manager', c.manager
) AS evidence
FROM down d
JOIN sold s ON s.listing_id = d.listing_id
JOIN listings l ON l.id = d.listing_id
JOIN products p ON p.id = l.product_id
JOIN categories c ON c.id = p.category_id
JOIN marketplaces m ON m.id = l.marketplace_id
WHERE s.units >= {min_units_7d}
ORDER BY started_at, l.id