`sql/classify.sql`

42 lines
-- TK-10875 unbuyable-catalog classification (READ-ONLY)
-- Target: dw_unified MIRROR (local /tmp socket). No writes.
--
-- "Unbuyable" = an ACTIVE product with NO sellable product variant
-- (only a $4.25 memo Sample variant, or a null buyability flag). The customer
-- can see it but cannot add a sellable roll/yard to cart.

-- 1) Headline count + cost-bucket split (row-level cost on shopify_products)
WITH unbuyable AS (
  SELECT * FROM shopify_products
  WHERE lower(status) = 'active'
    AND (has_product_variant IS DISTINCT FROM true)
)
SELECT
  (SELECT count(*) FROM unbuyable)                              AS unbuyable_total,
  (SELECT count(*) FROM unbuyable WHERE has_sample_variant)     AS has_sample,
  (SELECT count(*) FROM unbuyable WHERE COALESCE(cost,0) > 0)   AS bucket_A_row_cost,
  (SELECT count(*) FROM unbuyable WHERE COALESCE(cost,0) = 0)   AS bucket_B_no_row_cost,
  (SELECT count(*) FROM unbuyable WHERE has_product_variant IS NULL) AS null_flag;

-- 2) Vendor breakdown (where the cost-blocked long tail actually lives)
SELECT vendor, count(*) AS unbuyable,
       round(100.0*count(*)/sum(count(*)) over (),1) AS pct
FROM shopify_products
WHERE lower(status)='active' AND (has_product_variant IS DISTINCT FROM true)
GROUP BY vendor ORDER BY unbuyable DESC LIMIT 25;

-- 3) THE KEY CHECK the prior 8 read-only cycles never ran:
--    a real SKU-level JOIN of unbuyable products to staging cost.
--    Staging tables key the NUMERIC DW-SKU in `dw_sku`; shopify_products keys
--    the numeric DW-SKU in `sku` (its `dw_sku` holds the mfr-code form). So the
--    correct join is  shopify_products.sku = <vendor>_catalog.dw_sku.
--    (Joining dw_sku<->dw_sku or mfr_sku<->mfr_sku yields 0 — column-swap trap.)
WITH wg AS (
  SELECT id, sku FROM shopify_products
  WHERE vendor='Wolf Gordon' AND lower(status)='active'
    AND (has_product_variant IS DISTINCT FROM true)
)
SELECT count(*) AS wg_unbuyable,
       count(DISTINCT wg.id) FILTER (WHERE c.price_trade > 0) AS wg_recoverable
FROM wg LEFT JOIN wolf_gordon_catalog c ON c.dw_sku = wg.sku;