Loading…
Loading…
// case studies · real world impact
Solve free real-world SQL case studies from top companies — full schema, dataset, solution, and business insight. Great for interview prep.
// real world impact
Solve free real-world SQL case studies from top companies — full schema, dataset, solution, and business insight. Great for interview prep.
Browse Case Studies Interview questions// SELECT A CASE STUDY
SQL case study · Amazon
Generate personalized product recommendations for each user using collaborative filtering based on similar users' purchase and rating patterns.
Amazon's personalization team is prototyping a 'customers like you also bought' feature, and before the ML team productionizes anything, the underlying logic must be worked out — in SQL, on real data, where every step is inspectable.
The technique is collaborative filtering: people who rated like you will like what you haven't tried yet. Concretely, for target user Alice (user 1): find other users whose ratings overlap hers on at least 2 shared products, score their similarity with cosine similarity (the angle between two rating vectors — 1.0 means identical taste), keep those above 0.5, then surface products those similar users rated highly that Alice has never purchased, ranked by a similarity-weighted rating.
Watch the weak points an interviewer will poke: the cold start (a new user with no ratings breaks the whole chain), a top pick riding on a single rater's 5.0, and the arbitrary 0.5 cutoff. Name them before you're asked.
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)
);Your target
Generate personalized product recommendations for each user using collaborative filtering based on similar users' purchase and rating patterns.
Loading the expected output…
WITH target_user AS (
SELECT 1 AS user_id
),
similarity_scores AS (
SELECT
r1.user_id as similar_user_id,
tu.user_id as target_user_id,
ROUND(
(SUM(r1.rating * r2.rating) /
(SQRT(SUM(r1.rating * r1.rating)) * SQRT(SUM(r2.rating * r2.rating))))
, 3) as cosine_similarity
FROM ratings r1
CROSS JOIN target_user tu
JOIN ratings r2 ON r2.user_id = tu.user_id AND r2.product_id = r1.product_id
WHERE r1.user_id != tu.user_id
GROUP BY r1.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;
Step-by-step Solution:
Build user_rating_vectors CTE to collect all products each user has rated with their ratings, plus average rating per user.
Define target_user to isolate the user we're making recommendations for (user_id = 1).
Calculate similarity_scores using cosine similarity: for each other user, compute dot product of rating vectors divided by magnitude product. Only include user pairs with >= 2 common rated products for statistical significance. Filter for similarity > 0.5 (meaningful similarity).
Rank similar_users by cosine similarity and keep top similar users (threshold 0.5).
Build recommendations_unranked by finding products rated by similar users that the target user hasn't purchased. Exclude already-purchased items using LEFT JOIN on purchases with IS NULL filter. Weight each product's rating by the similarity score of the user who rated it.
Calculate weighted_rating (avg_rating * cosine_similarity) to reflect both product quality and similarity confidence. Count similar users recommending each product.
Rank recommendations by weighted_rating within target user. Assign priority tiers: TOP_PRIORITY (rank 1), HIGH (2-5), MEDIUM (6-10), LOW (11+). This drives 20-35% incremental revenue via personalized discovery.
Computing the reference output…
For Alice, the model surfaces Laptop Stand as the TOP_PRIORITY recommendation (weighted rating 4.99, driven by lookalike Bob's 5.0 rating on a product she has never bought), followed by Monitor Arm. The logic holds: recommend what similar high-raters love that the target user has not purchased yet.
What the interviewer asks next
“A brand-new user with zero ratings arrives — what does your collaborative filter recommend for them?”
Strong answer covers: Cold-start honesty: similarity needs history, so fall back to global signals (top-rated, trending, category bestsellers) and say exactly where the SQL logic hands off.
“Alice's TOP_PRIORITY pick rides on one similar user's 5.0 rating — robust signal or fragile?”
Strong answer covers: Single-rater risk: similar_users_count = 1 means one person's taste drives the top slot; propose minimum-rater thresholds or confidence weighting before shipping.
“The similarity cutoff is cosine > 0.5 with 2+ shared products — how would you tune those two knobs?”
Strong answer covers: Precision/recall tradeoff: stricter cutoffs give fewer, safer recs; looser ones give coverage with noise — tune against held-out purchases, not gut feel.
“The target user is hardcoded as user 1 — what changes to score recommendations for all five users at once?”
Strong answer covers: Generalization thinking: the PARTITION BY target_user_id scaffolding already anticipates it — replace the single-user CTE with the user list and reason about the compute blowup.
“Prices (Rs.599–Rs.5,999) appear in the output but never influence ranking — should a Rs.5,999 headphone outrank a Rs.299 cable on rating alone?”
Strong answer covers: Business-aware ranking: pure rating similarity ignores margin, affordability, and conversion likelihood — argue where price, margin, or stock should enter the score.
“Eve rated products 103 and 105 and bought both; Diana rated and bought both of hers — what breaks when ratings exist without purchases (browsing data)?”
Strong answer covers: Signal coverage: the LEFT JOIN against purchases assumes rating implies intent — with browse-only ratings, the exclusion logic needs rethinking or the model recommends what users already rejected.
Questions test whether your SQL runs. Cases test whether you can own a decision. Learn this once, use it everywhere below.
Restate the grain
One output row = one what? Write it down first — every GROUP BY and join must serve that grain.
Explore before you aggregate
Eyeball every table first: NULL join keys, orphan keys, date coverage. Ten minutes here saves an hour of debugging.
Hand-trace one entity end to end
Compute one row's answer by hand, then demand your query agree. Trust the trace, not the query.
Build incrementally, validate each CTE
Run each CTE standalone and reconcile counts before stacking the next one.
Pressure-test boundaries and empties
Probe cutoffs, zero-activity entities, ties, and empty results — interviewers live on boundaries.
Recommend, don't just report
End with a decision, an owner, and what would change your mind. No recommendation, no case study.