`data/d3b-held-verify.sql`

47 lines
\set ON_ERROR_STOP on
CREATE TEMP TABLE feed_tmp (
  alt_product_description text, number text, preferred_color_number text, list_price text,
  is_roll_price text, uom text, base_width text, category_name text, pattern_name text,
  preferred_color_name text, product_line_code text
);
\copy feed_tmp FROM 'data/momentum_feed_full.tsv' WITH (FORMAT csv, DELIMITER E'\t', HEADER true, QUOTE E'\x01')

WITH staged AS (
  SELECT pattern_name,
         lower(btrim(regexp_replace(pattern_name,'\s+',' ','g'))) AS np,
         lower(btrim(regexp_replace(color_name,'\s+',' ','g'))) AS nc,
         regexp_replace(replace(lower(btrim(regexp_replace(color_name,'\s+',' ','g'))),'gray','grey'),'[^a-z0-9]','','g') AS fc
  FROM momentum_colorways
),
staged_pairs  AS (SELECT DISTINCT np, nc FROM staged),
staged_fcolor AS (SELECT DISTINCT np, fc FROM staged),
canon_rank AS (
  SELECT np, pattern_name,
         row_number() OVER (PARTITION BY np ORDER BY count(*) DESC, length(pattern_name) ASC) rn
  FROM staged GROUP BY np, pattern_name
),
canon1 AS (SELECT np, pattern_name AS canon_raw FROM canon_rank WHERE rn=1),
feed_norm AS (
  SELECT number, pattern_name, preferred_color_name,
         lower(btrim(regexp_replace(pattern_name,'\s+',' ','g'))) AS np,
         lower(btrim(regexp_replace(preferred_color_name,'\s+',' ','g'))) AS nc,
         regexp_replace(replace(lower(btrim(regexp_replace(preferred_color_name,'\s+',' ','g'))),'gray','grey'),'[^a-z0-9]','','g') AS fc,
         row_number() OVER (PARTITION BY pattern_name, preferred_color_name ORDER BY number) AS dedup_rn
  FROM feed_tmp
  WHERE category_name IN ('Wallcovering','Acoustic')
    AND btrim(pattern_name) <> '' AND btrim(preferred_color_name) <> ''
),
feed_dedup AS (SELECT * FROM feed_norm WHERE dedup_rn = 1),
held AS (
  SELECT f.*, c.canon_raw, (c.canon_raw <> f.pattern_name) AS disambiguate
  FROM feed_dedup f
  JOIN canon1 c ON c.np = f.np
  LEFT JOIN staged_pairs  sp ON sp.np = f.np AND sp.nc = f.nc
  LEFT JOIN staged_fcolor sx ON sx.np = f.np AND sx.fc = f.fc
  WHERE sp.np IS NULL AND sx.np IS NULL
)
SELECT
  (SELECT count(*) FROM held) AS held_total,
  (SELECT count(*) FROM held WHERE NOT disambiguate) AS stage_new,
  (SELECT count(*) FROM held WHERE disambiguate) AS disambiguate;