Multi-Merchant Near-Duplicate Catalog Resolution with Price Consistency

-- Match identical product listings across merchants (high similarity + price within 20%)

SELECT a.sku_id AS sku_a,
       a.merchant AS merchant_a,
       a.title AS title_a,
       b.sku_id AS sku_b,
       b.merchant AS merchant_b,
       b.title AS title_b,
       round(array_cosine_similarity(a.vector, b.vector), 4) AS similarity,
       round(abs(a.price - b.price) / a.price * 100, 1) AS price_difference_pct
FROM merchant_catalog a
JOIN merchant_catalog b ON a.sku_id < b.sku_id
AND a.merchant != b.merchant
AND array_cosine_similarity(a.vector, b.vector) >= 0.90
AND abs(a.price - b.price) <= (0.20 * a.price)
ORDER BY similarity DESC;

CREATE TABLE merchant_catalog (sku_id INT, merchant VARCHAR, title VARCHAR, price decimal(10, 2),
                                                                                  vector FLOAT[3]);


INSERT INTO merchant_catalog
VALUES (1, 'Merchant A', 'Sony WH-1000XM5 Noise-Cancelling Headphones', 349.99, [0.95, 0.1, 0.05]::FLOAT[3]),
       (2, 'Merchant B', 'Sony WH1000XM5 Wireless Headset - Black', 339.00, [0.94, 0.12, 0.04]::FLOAT[3]),
       (3, 'Merchant C', 'Sony Headphone Replacement Ear Cushions', 29.99, [0.88, 0.2, 0.1]::FLOAT[3]);