Meta·Software Engineer·Technical Phone Screen·Senior

Senior

Apr 2026

Summary

Meta data engineering interview, SQL-heavy technical screen. One question, one concept, and honestly the window function angle is where people either nail it or start sweating.

Questions Asked (1)

Given an event log table with columns like event_date, follower_id, followee_id, and action (either 'follow' or 'unfollow'), write a SQL query that returns all follower/followee pairs who are still actively following each other as of a given target date D. Account for cases where a pair has toggled follow and unfollow multiple times.

Data ModelingAlgorithms & Data StructuresTechnical Trade-offs

Author's notes

The naive approach bites you here.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Use a window function to get the latest action per follower-followee pair before or on date D, then filter for pairs where both directions have 'follow' as the latest action. This handles multiple toggles by considering only the most recent state.

Pro tip: Mention that this approach assumes the event log is complete and that 'unfollow' events are always recorded; if not, you may need to handle missing unfollows. Also, discuss indexing on (follower_id, followee_id, event_date) for performance.

1. Understand the problem

Clarify that we need mutual follows as of date D, considering the latest action for each directed pair. Confirm that a pair is mutually following if both (A follows B) and (B follows A) have 'follow' as the latest action on or before D.

2. Get latest action per directed pair

Use a window function like ROW_NUMBER() OVER (PARTITION BY follower_id, followee_id ORDER BY event_date DESC) to rank actions for each directed pair, filtering for event_date <= D. Keep only the most recent action per pair.

3. Filter for active follows

From the ranked result, select pairs where the latest action is 'follow'. This gives all directed follow relationships that are active as of D.

4. Find mutual follows

Self-join the filtered result on follower_id = followee_id and followee_id = follower_id to find pairs where both directions are active follows. Ensure to avoid duplicates by selecting distinct pairs or using a canonical ordering.

5. Optimize and discuss trade-offs

Consider performance implications: indexing, partitioning, and whether to use a subquery or CTE. Discuss alternative approaches like using a self-join with NOT EXISTS for unfollows, and their trade-offs.

Key Points to Mention

Use of window functions (ROW_NUMBER, RANK) to handle multiple toggles and get the latest state.
Filtering events up to date D to reflect the state as of that date.
Self-join to find mutual follows, ensuring both directions are active.
Handling duplicates and ensuring distinct pairs in the output.
Performance considerations: indexing on (follower_id, followee_id, event_date) and partitioning.
Edge cases: pairs that never followed, pairs with only one direction, and pairs that unfollowed after D.

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