Pinterest·Data Scientist·Technical Phone Screen·Senior

Senior

Jun 2026

Summary

Pinterest data scientist interview with a pretty involved SQL question. One round, heavy on query writing and optimization thinking. Felt like a legitimate problem they actually care about, not a leetcode warmup.

Questions Asked (1)

Given two tables tracking lists and their items, write a SQL query that finds the unordered pair of recently created lists with the highest item overlap. Return the pair's IDs (smaller first), the overlap count, and the Jaccard similarity. Handle deduplication, ties, and be ready to explain indexing for scale plus a top-5 variant.

Data ModelingAlgorithms & Data StructuresTechnical Trade-offs

Author's notes

This one took me a minute to even parse.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and definitions (e.g., 'recently created', deduplication, tie-breaking). Then outline a self-join approach on list items to compute overlaps, using set operations for Jaccard similarity, and finally discuss indexing and a top-5 variant for scalability.

Pro tip: Mention that you would pre-aggregate item sets per list to avoid repeated scans, and use a hash-based join or bitmap indexes for large-scale overlap computation. Also, explicitly state how you'd handle ties (e.g., by smallest list IDs) to show attention to detail.

1. Clarify requirements and schema

Ask about table structures, what 'recently created' means (e.g., last N days or top K by creation date), and how to handle duplicates and ties. Confirm the output format.

2. Design the core query

Use a self-join on list items to find overlapping items between pairs of lists, ensuring each pair is considered once (list1.id < list2.id). Compute overlap count and Jaccard similarity as intersection over union.

3. Filter and rank

Filter to recently created lists, deduplicate items per list, and rank pairs by overlap count (or Jaccard) descending. Handle ties by defining a deterministic tie-breaker (e.g., smallest list IDs).

4. Optimize for scale

Discuss indexing strategies: index on list_id and item_id, consider covering indexes, and use partitioning or pre-aggregation for large datasets. Mention that Jaccard requires union size, which can be precomputed.

5. Extend to top-5 variant

Explain how to modify the query to return the top 5 pairs by overlap, using window functions or LIMIT with ORDER BY, and ensuring ties are handled consistently.

Key Points to Mention

Deduplication: use DISTINCT or GROUP BY to ensure each item is counted once per list.
Jaccard similarity formula: |A ∩ B| / |A ∪ B|, and how to compute union size efficiently.
Self-join with list1.id < list2.id to avoid duplicate pairs and self-pairs.
Indexing: composite index on (list_id, item_id) for fast joins; consider bitmap indexes for low-cardinality items.
Tie-breaking: define a deterministic order, e.g., by smallest list IDs or highest Jaccard.
Scalability: pre-filter recent lists, use temporary tables or CTEs, and consider approximate algorithms for very large scale.

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