1,175,454 Verified US 1P Retail SKUs. 420,382 Deterministic GTIN-12 Pairs. Zero AI Hallucinations.

SELECT w.upc_gtin, w.brand, w.title, w.price_usd AS walmart_usd, t.price_usd AS target_usd,
       ROUND((t.price_usd - w.price_usd) / w.price_usd * 100, 2) AS spread_pct
FROM 'walmart_1p.parquet' w
INNER JOIN 'target_1p.parquet' t ON w.upc_gtin = t.upc_gtin
WHERE w.in_stock = 1 AND t.in_stock = 1 AND ABS(t.price_usd - w.price_usd) > 1.50
ORDER BY spread_pct DESC LIMIT 100;

SELECT upc_gtin, ARRAY_AGG(DISTINCT retailer) AS banners, MIN(price_usd) AS floor_usd, MAX(price_usd) AS ceiling_usd
FROM 'hexa_fleet_1175454.parquet'
GROUP BY upc_gtin HAVING COUNT(DISTINCT retailer) >= 3
ORDER BY (MAX(price_usd) - MIN(price_usd)) DESC;

SELECT category, retailer, COUNT(*) AS basket_skus, ROUND(AVG(price_usd), 2) AS basket_avg_usd
FROM 'hexa_fleet_1175454.parquet'
WHERE is_verified_1p = 1
GROUP BY 1, 2 ORDER BY 1, 4 ASC;

SELECT length(upc_gtin) AS gtin_len, COUNT(*) AS verified_rows
FROM 'hexa_fleet_1175454.parquet'
GROUP BY 1 ORDER BY 2 DESC;

SELECT retailer, COUNT(*) AS verified_1p_skus, MIN(price_usd) AS min_usd, MAX(price_usd) AS max_usd
FROM 'hexa_fleet_1175454.parquet'
WHERE is_verified_1p = 1 GROUP BY 1;

INSTALL httpfs; LOAD httpfs;
SELECT COUNT(*), COUNT(DISTINCT upc_gtin) FROM read_parquet('s3://thatoneguypondering-deliveries/hexa_fleet_1175454.parquet');

SELECT gs1_prefix, brand, retailer, COUNT(*) AS skus, ROUND(AVG(price_usd), 2) AS mean_shelf_usd
FROM 'hexa_fleet_1175454.parquet'
WHERE brand IN ('Good & Gather', 'Great Value', 'Simple Truth', 'Meijer')
GROUP BY 1, 2, 3 ORDER BY 4 DESC;

SELECT m.canonical_gtin14, m.title, m.price_usd AS meijer_usd, k.price_usd AS kroger_usd, w.price_usd AS walmart_usd
FROM 'meijer_1p.parquet' m
JOIN 'kroger_1p.parquet' k USING (canonical_gtin14)
JOIN 'walmart_1p.parquet' w USING (canonical_gtin14)
WHERE m.in_stock = 1 AND k.in_stock = 1;

SELECT canonical_gtin14, brand, title, model, MIN(price_usd) AS floor_rcv_usd, MAX(price_usd) AS ceiling_rcv_usd
FROM 'hexa_fleet_1175454.parquet'
WHERE category IN ('ELECTRONICS', 'APPLIANCES', 'TOOLS & HARDWARE')
GROUP BY 1, 2, 3, 4;