Rails 24:00 Orvane Home & Outdoor

← Definitions

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

Run the tests on this world

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.

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

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

Tests: count_between unique