Shared Category Purchasers

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

Meta

Problem

The recommendations team is building a collaborative filtering feature and wants co-purchasers who shop the same aisle. For each product category, take every pair of users who both bought in that category and count the ways one user's purchases in that category combine with the other's, reporting each pair only once with the smaller of the two user IDs as the pair's first member.

They bought different things from the same aisle.

Tables

transactions

Columns: transaction_id INTEGER, user_id INTEGER, product_id INTEGER, quantity INTEGER, total_amount REAL, transaction_date TEXT

products

Columns: product_id INTEGER, product_name TEXT, category TEXT, price REAL, rating REAL, in_stock INTEGER

Expected output

Worked solution and explanation

Why this problem exists in real interviews

This is a per-category pairwise co-occurrence dressed up as a recommender feature. The real skill is joining transactions to itself so two different buyers meet in one row, then canonicalizing each unordered pair with t1.user_id < t2.user_id. Anyone can group by user_id and category; that only counts what one person bought and never relates two people. The trap is the join predicate: reach for <> and you emit every pair twice in mirror order plus self-pairs, doubling and corrupting the collaborative-filtering signal. Note what the count is: COUNT(*) over the self-join is the number of ways to pair one buyer's purchases in a category with the other buyer's, not a set intersection of the same product.

Break down the requirements

Step 1: Self-join the fact table to pair buyers

Join transactions t1 to transactions t2 on t1.user_id < t2.user_id. The strict inequality does double duty: it drops self-pairs (a user can never satisfy id < id) and it keeps only one of the two mirror orderings, so each unordered pair surfaces exactly once with the smaller user ID as user1.

Step 2: Scope the pairing to a single category

Bring in products twice, once per buyer, and require p.category = p2.category. Without that predicate the two sides fan out across every category combination and the pairing you count becomes meaningless.

Step 3: Count the pairings per category and pair

GROUP BY p.category, t1.user_id, t2.user_id and COUNT(*) counts, per category per pair, every way one buyer's purchase in that category lines up with the other buyer's. Each output row is one category and one canonical pair with user1 the smaller ID, holding the pairing count in shared_categories.

The solution

SELECT p.category, t1.user_id AS user1, t2.user_id AS user2, COUNT(*) AS shared_categories
FROM transactions t1
INNER JOIN transactions t2 ON t1.user_id < t2.user_id
INNER JOIN products p ON t1.product_id = p.product_id
INNER JOIN products p2 ON t2.product_id = p2.product_id AND p.category = p2.category
GROUP BY p.category, t1.user_id, t2.user_id

Common follow-up questions

• On 100M `transactions`, the self-join is the expensive step. How would you bound it, for example by pairing only within a single category or date partition at a time? (Tests understanding that the self-join is the cost driver and that partitioning on `transaction_date` bounds the pairing.)
• How would you turn the raw pairing count into a normalized affinity score so a pair with a few purchases in a small category isn't buried under a pair with many in a huge one? (Tests whether the candidate can generalize the pair count into a similarity metric.)
• If a user buys the same `product_id` twice, that inflates the pairing count with a co-buyer. Should it, and how would you change the count if not? (Tests handling of duplicate purchases of the same product by the same user.)