Google·Data Scientist·Technical Phone Screen·Senior

Senior

Jun 2026

Summary

SQL heavy technical screen for a Data Scientist role at Google. One question, but it had enough moving parts that I was mentally juggling constraints the whole time and probably talked too much before writing anything.

Questions Asked (1)

Given a schema with orders and order_items tables, write SQL to find the top 5 product pairs most frequently bought together across distinct orders. Each pair should be counted at most once per order, no self-pairs, smaller product_id listed first, ties broken by pair count then lexicographic order. Return p1_id, p2_id, and pair_count. Also suggest indexes to make this fast on large data.

Data ModelingAlgorithms & Data StructuresSystem Design

Author's notes

The self-join to generate pairs tripped me up at first because I kept second-guessing the less-than condition for ensuring smaller id comes first.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and requirements, then outline a SQL solution using a self-join on order_items within the same order to generate product pairs, ensuring each pair is counted once per order. Finally, aggregate, rank, and filter to get the top 5 pairs, and discuss indexing strategies to optimize performance on large datasets.

Pro tip: Mention that you would use a CTE to deduplicate pairs per order and then aggregate, which avoids double-counting and improves readability. Also, consider using window functions like ROW_NUMBER() for tie-breaking and efficient ranking.

1. Understand the schema and requirements

Clarify the columns in orders and order_items, and confirm that pairs are counted per distinct order, with no self-pairs and smaller product_id first.

2. Generate distinct product pairs per order

Use a self-join on order_items within the same order_id, ensuring product_id1 < product_id2 to avoid duplicates and self-pairs, and select distinct pairs per order.

3. Aggregate and rank pairs

Count occurrences of each pair across orders, then rank them by count descending and lexicographic order of product IDs, using window functions or ORDER BY with LIMIT.

4. Select top 5 pairs

Filter the ranked results to return only the top 5 pairs, ensuring ties are broken correctly.

5. Suggest indexes for performance

Recommend composite indexes on order_items(order_id, product_id) and possibly on product_id to speed up joins and aggregations on large data.

Key Points to Mention

Use of self-join on order_items with condition oi1.product_id < oi2.product_id to avoid self-pairs and ensure consistent ordering.
Deduplication of pairs per order using DISTINCT or GROUP BY to count each pair at most once per order.
Aggregation with COUNT(DISTINCT order_id) or COUNT(*) after deduplication to get pair frequency.
Tie-breaking logic: ORDER BY pair_count DESC, p1_id ASC, p2_id ASC.
Indexing strategy: composite index on (order_id, product_id) for efficient joins and filtering, and possibly on product_id for sorting.
Consider scalability: partitioning, materialized views, or approximate algorithms if data is extremely large.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.