Microsoft·Data Scientist·Technical Phone Screen
Jun 2026
SQL-heavy technical screen for a Data Scientist role at Microsoft. The whole thing revolved around a single graph problem, building up from a basic reciprocal-edge join to a full common-friends query with deduplication and then index design. Pretty intense for one problem but it tested a lot of ground.
- Given a directed edge table recording who followed whom, write a single SQL query to produce an undirected friendship table with one row per mutual connection, where the first user column is always less than the second.
- Without using temp tables, write a single SQL query that finds all common friends for every unordered pair of distinct users (x, y) where x < y. A common friend must be mutually connected to both x and y, and the pair itself should not count as its own friend.
- Extend the previous query to also return the count of distinct common friends per pair. How do you ensure no duplicates appear even if the underlying edge table has redundant reciprocal rows?
- What indexes would you add to the FriendEdges table to make the common-friends query performant at 100 million rows, and why?
“The self-join on reciprocal rows is straightforward enough, but the u1 < u2 constraint to avoid duplicate pairs is the part I almost forgot.”