montanalow

CREATE TABLE buy_it_again (
  customer_id INTEGER NOT NULL,
  product_ids INTEGER[] NOT NULL,
  purchase_counts INTEGER[] NOT NULL
);

montanalow

CREATE TABLE buy_it_again (
  customer_id INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  purchase_count INTEGER NOT NULL
);

montanalow

ALTER TABLE products 
ADD COLUMN sales_volume integer;

montanalow

-- Denormalize keywords onto items
CREATE FUNCTION denormalize_product_keywords_to_items() RETURNS trigger
  LANGUAGE plpgsql
AS $$
  begin
    select keywords into NEW.keywords from products where NEW.product_id = products.product_id;
    return NEW;
  end
$$;

montanalow

EXPLAIN ANALYZE
SELECT count(*)
FROM products
WHERE keywords @@ to_tsquery('apple');

montanalow

SELECT *
FROM products
NATURAL JOIN items
WHERE products.keywords @@ to_tsquery('apple') -- full text query
  AND items.retailer_location_id = 123 -- a single store's inventory

montanalow

CREATE INDEX products_search_index 
ON products 
USING gin(keywords);

montanalow

ALTER TABLE products 
ADD COLUMN keywords tsvector -- the text search indexable data type
GENERATE ALWAYS AS (
  set_weight(to_tsvector(name), 'A') ||
  set_weight(to_tsvector(description), 'B')
) STORED;

montanalow

INSERT INTO … 
SELECT * FROM temp_staging_table -- first prepare the data by preloading into a temp table
ON CONFLICT … DO UPDATE SET … -- upsert on the primary key
WHERE updated_at <= excluded.updated_at -- ensure idempotent jobs
  AND ( -- only write new distinct data
    column_1 IS DISTINCT FROM excluded.column_1 OR 
    column_2 IS DISTINCT FROM excluded.column_2 OR
    …
  )

montanalow

SELECT *
FROM products
NATURAL JOIN search_product_conversions 
NATURAL JOIN items
WHERE items.keywords @@ ts_query('fuji | apple')
  AND items.retailer_location_id = 123
  AND search_products_conversions.query = 'fuji apple'
ORDER BY 
  (100 * search_product_conversions.count) + 
  ( 10 * products.popularty) +