Business Problem

Amazon's personalization team is prototyping a 'customers like you also bought' feature and needs the underlying logic worked out in SQL before handing it to the ML team for productionization. For a given target user, find other users with similar purchase and rating behavior, then surface products those similar users rated highly that the target user has not purchased yet — ranked by a combination of how similar the other users are and how highly they rated the product — so the top rows can be shown directly on the user's homepage.

Dataset & Schema

User data with user_id; product ratings with user_id, product_id, rating, review_date; purchase history with user_id, product_id, purchase_date; product catalog with product_id, category, price.

CREATE TABLE users (
  user_id INTEGER PRIMARY KEY,
  user_name TEXT,
  signup_date DATE
);
CREATE TABLE ratings (
  rating_id INTEGER PRIMARY KEY,
  user_id INTEGER,
  product_id INTEGER,
  rating DECIMAL(2,1),
  review_date DATE,
  FOREIGN KEY(user_id) REFERENCES users(user_id)
);
CREATE TABLE purchases (
  purchase_id INTEGER PRIMARY KEY,
  user_id INTEGER,
  product_id INTEGER,
  purchase_date DATE,
  FOREIGN KEY(user_id) REFERENCES users(user_id)
);
CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT,
  category TEXT,
  price DECIMAL(10,2)
);

SQL Solution

WITH user_rating_vectors AS (
  SELECT 
    user_id,
    COLLECT_LIST(STRUCT(product_id, rating)) as products_rated,
    COUNT(DISTINCT product_id) as total_rated,
    AVG(rating) as avg_rating
  FROM ratings
  GROUP BY user_id
),
target_user AS (
  SELECT 
    user_id,
    products_rated,
    total_rated,
    avg_rating
  FROM user_rating_vectors
  WHERE user_id = 1
),
similarity_scores AS (
  SELECT 
    urv.user_id as similar_user_id,
    tu.user_id as target_user_id,
    ROUND(
      (SUM(r1.rating * r2.rating) / 
        (SQRT(SUM(POWER(r1.rating, 2))) * SQRT(SUM(POWER(r2.rating, 2))))) 
      , 3) as cosine_similarity
  FROM user_rating_vectors urv
  CROSS JOIN target_user tu
  LEFT JOIN ratings r1 ON urv.user_id = r1.user_id
  LEFT JOIN ratings r2 ON tu.user_id = r2.user_id 
    AND r1.product_id = r2.product_id
  WHERE urv.user_id != tu.user_id
  GROUP BY urv.user_id, tu.user_id
  HAVING COUNT(DISTINCT r1.product_id) >= 2
),
top_similar_users AS (
  SELECT 
    similar_user_id,
    target_user_id,
    cosine_similarity,
    ROW_NUMBER() OVER (PARTITION BY target_user_id ORDER BY cosine_similarity DESC) as rank
  FROM similarity_scores
  WHERE cosine_similarity > 0.5
),
recommendations_unranked AS (
  SELECT 
    tsu.target_user_id,
    r.product_id,
    p.product_name,
    p.category,
    p.price,
    ROUND(AVG(r.rating) * tsu.cosine_similarity, 2) as weighted_rating,
    COUNT(DISTINCT tsu.similar_user_id) as similar_users_count,
    ROUND(AVG(r.rating), 2) as avg_similar_user_rating
  FROM top_similar_users tsu
  INNER JOIN ratings r ON tsu.similar_user_id = r.user_id
  INNER JOIN products p ON r.product_id = p.product_id
  LEFT JOIN purchases pu ON tsu.target_user_id = pu.user_id 
    AND r.product_id = pu.product_id
  WHERE pu.purchase_id IS NULL
  GROUP BY tsu.target_user_id, r.product_id, p.product_name, p.category, p.price
),
final_recommendations AS (
  SELECT 
    target_user_id,
    product_id,
    product_name,
    category,
    price,
    weighted_rating,
    similar_users_count,
    avg_similar_user_rating,
    ROW_NUMBER() OVER (PARTITION BY target_user_id ORDER BY weighted_rating DESC) as recommendation_rank
  FROM recommendations_unranked
)
SELECT 
  target_user_id,
  product_id,
  product_name,
  category,
  price,
  weighted_rating,
  similar_users_count,
  avg_similar_user_rating,
  CASE 
    WHEN recommendation_rank = 1 THEN 'TOP_PRIORITY'
    WHEN recommendation_rank <= 5 THEN 'HIGH'
    WHEN recommendation_rank <= 10 THEN 'MEDIUM'
    ELSE 'LOW'
  END as recommendation_priority
FROM final_recommendations
WHERE recommendation_rank <= 10
ORDER BY target_user_id, recommendation_rank;

Explanation