Price below cost price_below_cost
A listing selling under what the product costs, without an approved markdown: usually a price-feed sync gone wrong.
Parameters: margin_floor = 0.0
trading · v1 · trading lead
The listing's price as of now (the latest price sync, else an active promotion, else the list price) is below the product's unit cost plus margin_floor, the listing is not paused, and no approved below-cost promotion covers this listing at this price. Approvals are recorded per listing: a loss leader approved on the own shop and copied to a marketplace by hand is listed until its marketplace listing is approved too.
WITH synced AS (
SELECT listing_id, arg_max(CAST(json_extract(payload, '$.price') AS DOUBLE), "at") AS price,
arg_max(json_extract_string(payload, '$.source'), "at") AS source, max("at") AS changed_at
FROM listing_events WHERE type = 'price_sync' AND "at" <= {now} GROUP BY listing_id
),
promo AS (
SELECT listing_id, promo_price, below_cost_approved, approved_by, starts_at
FROM promotions WHERE starts_at <= {now} AND ends_at > {now}
),
paused AS (
SELECT listing_id, arg_max(type, "at") AS state
FROM listing_events WHERE type IN ('paused', 'resumed') AND "at" <= {now} GROUP BY listing_id
),
priced AS (
SELECT l.id AS listing_id, l.product_id, l.marketplace_id, l.price AS list_price,
CASE WHEN s.price IS NOT NULL AND (pr.listing_id IS NULL OR s.changed_at >= pr.starts_at) THEN s.price
WHEN pr.listing_id IS NOT NULL THEN pr.promo_price ELSE l.price END AS price,
CASE WHEN s.price IS NOT NULL AND (pr.listing_id IS NULL OR s.changed_at >= pr.starts_at) THEN s.changed_at
WHEN pr.listing_id IS NOT NULL THEN pr.starts_at END AS since,
CASE WHEN s.price IS NOT NULL AND (pr.listing_id IS NULL OR s.changed_at >= pr.starts_at) THEN s.source
WHEN pr.listing_id IS NOT NULL THEN 'promotion' ELSE 'list price' END AS source,
coalesce(pr.below_cost_approved, FALSE) AND pr.promo_price IS NOT NULL AS approved,
pr.promo_price
FROM listings l
LEFT JOIN synced s ON s.listing_id = l.id
LEFT JOIN promo pr ON pr.listing_id = l.id
WHERE s.listing_id IS NOT NULL OR pr.listing_id IS NOT NULL
),
sold AS (
SELECT ol.listing_id, count(*) AS orders, round(sum(ol.qty * (p.unit_cost - ol.unit_price)), 2) AS loss
FROM order_lines ol JOIN orders o ON o.id = ol.order_id JOIN products p ON p.id = ol.product_id
WHERE o.placed_at <= {now} AND o.placed_at > {now} - INTERVAL 1 DAY AND ol.unit_price < p.unit_cost
GROUP BY ol.listing_id
)
SELECT 'listing' AS subject_type, x.listing_id AS subject_id, x.listing_id, x.product_id, x.marketplace_id,
'listing:' || x.listing_id AS scope, x.since AS started_at,
CASE WHEN x.price < p.unit_cost * 0.5 THEN 'high' ELSE 'medium' END AS severity,
json_object(
'sku', p.sku, 'product', p.name, 'marketplace', m.name, 'price', x.price, 'unit_cost', p.unit_cost,
'margin_pct', round((x.price - p.unit_cost) / x.price * 100, 1), 'list_price', x.list_price,
'promo_price', x.promo_price, 'price_source', x.source, 'priced_since', x.since,
'orders_below_cost_24h', coalesce(so.orders, 0), 'loss_24h', coalesce(so.loss, 0),
'category_manager', c.manager
) AS evidence
FROM priced x
JOIN products p ON p.id = x.product_id
JOIN categories c ON c.id = p.category_id
JOIN marketplaces m ON m.id = x.marketplace_id
LEFT JOIN paused pa ON pa.listing_id = x.listing_id
LEFT JOIN sold so ON so.listing_id = x.listing_id
WHERE x.price < p.unit_cost * (1 + {margin_floor})
AND coalesce(pa.state, 'resumed') <> 'paused'
AND NOT (x.approved AND x.price = x.promo_price)
ORDER BY started_at, x.listing_id