Write SQL for active follow connections
Company: Meta
Role: Data Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
Table: follow_events(requester_id INT, target_id INT, event STRING CHECK (event IN ('request_follow','follow_success','follow_reject','unfollow')), event_ts TIMESTAMP).
Rules:
- For a pair (A,B), an active connection exists at time T if the most recent event at or before T is 'follow_success' and there is no later 'unfollow' at or before T.
- 'follow_reject' and pending 'request_follow' do not create an active connection; multiple request/accept cycles may occur.
Tasks:
1) Write SQL to return the current number of active follow connections (treat T = now()).
2) Write SQL to return, for each calendar day, the count of active follow connections at the end of that day.
Example sequence to reason about: A request_follow B at t1; A follow_success B at t2; A unfollow B at t3 ⇒ not active after t3.
Overview: This question evaluates proficiency in temporal event modeling, time-aware SQL aggregation, and reasoning about state transitions from ordered event streams, testing competencies in building point-in-time and historical views within the Data Manipulation (SQL/Python) domain for data engineering roles, with a primary level of practical application augmented by temporal-conceptual understanding. It is commonly asked to assess the ability to infer current and daily historical relationships from event histories, manage edge cases like multiple request/accept cycles and rejections, and produce efficient queries for current counts and daily snapshots.
Read the full Meta Data Engineer interview experience this question came from
Current number of active follow connections (as of a fixed 'now')
You are given a table of follow lifecycle events.
Table: follow_events(requester_id INT, target_id INT, event VARCHAR CHECK (event IN ('request_follow','follow_success','follow_reject','unfollow')), event_ts TIMESTAMP).
Rules:
- For a pair (A,B), an active connection exists at time T if the most recent event at or before T is 'follow_success'.
- 'follow_reject' and pending 'request_follow' do not create an active connection.
- Multiple request/accept/unfollow cycles may occur for the same pair.
Task: Write a SQL query to return the current number of active follow connections as of T = TIMESTAMP '2025-06-01 12:00:00'.
Return one row with one column: active_connections.
Tables
follow_events(requester_id INT, target_id INT, event VARCHAR(20), event_ts TIMESTAMP)
Hints
- Reduce each (requester_id, target_id) pair to its single most recent event at or before the as-of timestamp.
- If that last event is 'follow_success' then the connection is active; otherwise it is not.
Daily active follow connections at end of day
Using the same follow_events table and the same rules for whether a (requester_id, target_id) pair is active at time T, write a SQL query to return the count of active follow connections at the end of each calendar day from 2025-05-27 through 2025-06-01 inclusive.
For each day D, measure activity at D 23:59:59.
Output columns:
- calendar_day (DATE)
- active_connections_eod (INT)
Order by calendar_day ascending.
Tables
follow_events(requester_id INT, target_id INT, event VARCHAR(20), event_ts TIMESTAMP)
Hints
- Turn event streams into time intervals: each 'follow_success' begins an interval that ends at the next event for the same pair.
- Count distinct pairs whose interval contains the timestamp D 23:59:59 for each date D.
Community answers
Answer by ginb
q1
SELECT COUNT(*) AS active_connections
FROM (
SELECT
event,
ROW_NUMBER() OVER(
PARTITION BY requester_id, target_id
ORDER BY event_ts DESC
) as rank
FROM follow_events
) latest_events
WHERE rank = 1 AND event = 'follow_success';
Answer by ginb
q2
WITH daily_spine AS (
-- Generate all dates in the range of the data
SELECT DISTINCT DATE(event_ts) as report_date
FROM follow_events
),
pair_statuses AS (
-- Get the status of every pair at the end of every day they had an event
SELECT
requester_id,
target_id,
DATE(event_ts) as event_date,
event,
ROW_NUMBER() OVER(
PARTITION BY requester_id, target_id, DATE(event_ts)
ORDER BY event_ts DESC
) as daily_rank
FROM follow_events
),
latest_status_per_day AS (
-- Filter for the last event of the day for each pair
SELECT requester_id, target_id, event_date, event
FROM pair_statuses
WHERE daily_rank = 1
),
state_on_date AS (
-- For every date in the spine, find the most recent status for every pair
-- that has ever interacted up to that date
SELECT
d.report_date,
s.requester_id,
s.target_id,
s.event,
ROW_NUMBER() OVER(
PARTITION BY d.report_date, s.requester_id, s.target_id
ORDER BY s.event_date DESC
) as current_state_rank
FROM daily_spine d
JOIN latest_status_per_day s ON s.event_date <= d.report_date
)
-- Count pairs where the most recent state at the end of the day was success
SELECT
report_date,
COUNT(*) AS active_connections
FROM state_on_date
WHERE current_state_rank = 1 AND event = 'follow_success'
GROUP BY report_date
ORDER BY report_date;