Count active follow connections
Company: Meta
Role: Data Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
##### Question
Write SQL to return the current number of active follow connections.
Events table columns: user_id, target_id, event_type ('request_follow', 'follow_success', 'follow_reject', 'unfollow'), event_time.
A connection is active only after a 'follow_success' that has not been undone by a later 'unfollow'.
Overview: This question evaluates the ability to manipulate temporal event data and reconstruct current state using SQL or Python, focusing on event sequencing, aggregation, and deduplication competencies.
Assume the current date is 2025-06-01. Using the full Events history up to that date, write SQL to return the current number of active follow connections in the system. A connection between (user_id, target_id) is active if there has been at least one 'follow_success' event for that pair and it has not been undone by a later 'unfollow' event. In other words, for each (user_id, target_id) pair, look at the latest change between 'follow_success' and 'unfollow'; the connection is active only if this latest change is 'follow_success'. Return a single row with the total count of active connections.
Tables
Events(user_id INTEGER, target_id INTEGER, event_type VARCHAR(20), event_time TIMESTAMP)
Hints
- Consider only the latest state per (user_id, target_id) pair.
- Compare the most recent 'follow_success' time to the most recent 'unfollow' time for each pair.
Community answers
Answer by ginb
SELECT count(*) as active_connections
FROM (
select user_id,
target_id,
event_type,
row_number() over (partition by user_id, target_id order by event_time desc) as rn
from events
) fs
where rn=1 and event_type='follow_success'