Write SQL for active follow connections

Q: Write SQL for active follow connections

This is a Data Manipulation (SQL/Python) interview question from Meta for Data Engineer roles. View the full question and solution on PracHub.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

Question

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:

Write SQL to return the current number of active follow connections (treat T = now()).
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.

Write SQL for active follow connections

Comments (0)