Meta·Software Engineer·Technical Phone Screen·Senior

Senior

Jun 2026

Summary

SQL round at Meta for a data engineer role. One question, pretty focused on modeling follow relationships correctly. Not a lot of fluff, just write the query and explain your logic.

Questions Asked (1)

Given an events table with columns for user_id, target_id, event_type, and event_time (where event types include 'request_follow', 'follow_success', 'follow_reject', and 'unfollow'), write a SQL query that returns the current count of active follow connections. A connection counts as active only if there's been a 'follow_success' with no subsequent 'unfollow' for that same user-target pair.

Data ModelingProduct Analytics & Metrics

Author's notes

The part that trips people up is the 'no subsequent unfollow' condition.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Clarify the definition of an active follow connection and the grain of the events table, then outline a SQL strategy that identifies the latest relevant event per user-target pair and filters for 'follow_success'. Use window functions or aggregation to determine the current state, and discuss edge cases like multiple follows/unfollows and null handling.

Pro tip: Mention that you would validate the query against sample data and consider performance implications, such as indexing on (user_id, target_id, event_time) and using incremental processing if the table is large.

1. Clarify requirements and assumptions

Confirm that 'active' means the most recent event for a user-target pair is 'follow_success', and that 'unfollow' is the only event that deactivates a connection. Ask about data volume, update frequency, and whether historical states are needed.

2. Identify the latest event per pair

Use a window function like ROW_NUMBER() OVER (PARTITION BY user_id, target_id ORDER BY event_time DESC) to rank events, or use aggregation with MAX(event_time) to find the most recent event for each pair.

3. Filter for active connections

Select pairs where the latest event_type is 'follow_success'. This ensures no subsequent 'unfollow' has occurred.

4. Count and handle edge cases

Count the distinct user-target pairs. Consider edge cases: multiple events at the same timestamp, missing events, and whether 'follow_reject' or 'request_follow' affect the state (they do not, per definition).

5. Write and optimize the query

Compose the final SQL, using CTEs for readability. Discuss indexing and partitioning strategies for large-scale data, and mention potential incremental computation if the table is append-only.

Key Points to Mention

Use of window functions (e.g., ROW_NUMBER, RANK) to get the latest event per user-target pair.
Handling ties in event_time by defining a deterministic order (e.g., by event_type priority or event_id).
The importance of partitioning by user_id and target_id to correctly group events.
Performance considerations: indexing on (user_id, target_id, event_time) and avoiding full table scans.
Edge cases: multiple follow/unfollow cycles, events with identical timestamps, and null values.
Clarifying that 'request_follow' and 'follow_reject' do not affect the active state.

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