Review bomb after a bad batch review_bomb
A product that suddenly collects one- and two-star reviews, usually because one delivery lot is faulty. Checked every hour.
Parameters: lookback_hours = 72 min_bad = 5 min_bad_share = 0.4 min_prior_rating = 3.5
Test results
| meaning | test | passed | observed | expected |
|---|---|---|---|---|
| quality | count_between | passed | 0 | between 0.0 and 10.0 |
| quality | unique | passed | {'duplicates': 0} | product_id unique |
| quality | detector_shape | passed | {'missing_columns': [], 'bad_rows': 0, 'rows': 0} | columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object |
quality · v1 · quality lead
In the last lookback_hours at least min_bad reviews of two stars or fewer, at least min_bad_share of the product's reviews in that time, on a product that averaged at least min_prior_rating over the 60 days before. The evidence names the lot most of the bad reviews came from; a product whose worst lot is already quarantined is not listed.
WITH recent AS (
SELECT product_id, lot_id, rating, "at" FROM reviews
WHERE "at" <= {now} AND "at" > {now} - INTERVAL ({lookback_hours}) HOUR
),
agg AS (
SELECT product_id, count(*) AS reviews, count(*) FILTER (WHERE rating <= 2) AS bad,
min("at") FILTER (WHERE rating <= 2) AS first_bad
FROM recent GROUP BY product_id
HAVING count(*) FILTER (WHERE rating <= 2) >= {min_bad}
AND count(*) FILTER (WHERE rating <= 2) >= {min_bad_share} * count(*)
),
prior AS (
SELECT r.product_id, avg(r.rating) AS rating, count(*) AS n
FROM reviews r JOIN agg a ON a.product_id = r.product_id
WHERE r.at <= {now} - INTERVAL ({lookback_hours}) HOUR AND r.at > {now} - INTERVAL 60 DAY
GROUP BY r.product_id
),
worst AS (
SELECT product_id, arg_max(lot_id, n) AS lot_id, max(n) AS lot_bad
FROM (SELECT product_id, lot_id, count(*) AS n FROM recent WHERE rating <= 2 GROUP BY ALL)
GROUP BY product_id
),
defects AS (
SELECT r.product_id, count(*) AS defective FROM returns r JOIN agg a ON a.product_id = r.product_id
WHERE r.reason = 'defective' AND r.requested_at <= {now} AND r.requested_at > {now} - INTERVAL 7 DAY
GROUP BY r.product_id
)
SELECT 'product' AS subject_type, p.id AS subject_id, p.id AS product_id, wo.lot_id, lt.warehouse_id,
'product:' || p.id AS scope, a.first_bad AS started_at,
CASE WHEN a.bad >= 10 THEN 'high' ELSE 'medium' END AS severity,
json_object(
'sku', p.sku, 'product', p.name, 'reviews', a.reviews, 'bad_reviews', a.bad,
'bad_share', round(a.bad / a.reviews, 3), 'prior_rating', round(pr.rating, 2),
'lot_code', lt.lot_code, 'lot_bad_reviews', wo.lot_bad, 'lot_received_at', lt.received_at,
'defective_returns_7d', coalesce(d.defective, 0), 'category_manager', c.manager
) AS evidence
FROM agg a
JOIN products p ON p.id = a.product_id
JOIN categories c ON c.id = p.category_id
LEFT JOIN prior pr ON pr.product_id = a.product_id
LEFT JOIN worst wo ON wo.product_id = a.product_id
LEFT JOIN lots lt ON lt.id = wo.lot_id
LEFT JOIN defects d ON d.product_id = a.product_id
WHERE coalesce(pr.rating, 5) >= {min_prior_rating} AND coalesce(lt.status, 'released') <> 'quarantined'
ORDER BY started_at, p.id