Coding questions · Meta
Write SQL for active follow connections
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.
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.
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.
Table follow_events
| column | type | constraints |
|---|---|---|
| requester_id | INT | NOT NULL |
| target_id | INT | NOT NULL |
| event | VARCHAR(20) | NOT NULL |
| event_ts | TIMESTAMP | NOT NULL |
Sample data:
| event | event_ts | requester_id | target_id |
|---|---|---|---|
| request_follow | 2025-05-27 09:00:00 | 1 | 2 |
| follow_success | 2025-05-27 10:00:00 | 1 | 2 |
| unfollow | 2025-05-29 08:00:00 | 1 | 2 |
| request_follow | 2025-05-30 09:00:00 | 1 | 2 |
| follow_success | 2025-05-30 11:00:00 | 1 | 2 |
| request_follow | 2025-05-28 12:00:00 | 1 | 3 |
| follow_reject | 2025-05-28 13:00:00 | 1 | 3 |
| request_follow | 2025-05-31 15:00:00 | 1 | 3 |
| request_follow | 2025-05-28 09:00:00 | 2 | 3 |
| follow_success | 2025-05-28 09:30:00 | 2 | 3 |
| unfollow | 2025-06-01 09:00:00 | 2 | 3 |
| follow_success | 2025-05-31 23:00:00 | 4 | 2 |
| request_follow | 2025-05-29 10:00:00 | 5 | 6 |
| follow_success | 2025-05-27 14:00:00 | 7 | 8 |
| unfollow | 2025-05-27 16:00:00 | 7 | 8 |
| follow_success | 2025-05-27 17:00:00 | 7 | 8 |
| follow_reject | 2025-05-27 18:00:00 | 7 | 8 |
Expected output
One row with the total number of (requester_id, target_id) pairs whose latest event at or before 2025-06-01 12:00:00 is 'follow_success'.
| active_connections |
|---|
| 2 |
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.
Table follow_events
| column | type | constraints |
|---|---|---|
| requester_id | INT | NOT NULL |
| target_id | INT | NOT NULL |
| event | VARCHAR(20) | NOT NULL |
| event_ts | TIMESTAMP | NOT NULL |
Sample data:
| event | event_ts | requester_id | target_id |
|---|---|---|---|
| request_follow | 2025-05-27 09:00:00 | 1 | 2 |
| follow_success | 2025-05-27 10:00:00 | 1 | 2 |
| unfollow | 2025-05-29 08:00:00 | 1 | 2 |
| request_follow | 2025-05-30 09:00:00 | 1 | 2 |
| follow_success | 2025-05-30 11:00:00 | 1 | 2 |
| request_follow | 2025-05-28 12:00:00 | 1 | 3 |
| follow_reject | 2025-05-28 13:00:00 | 1 | 3 |
| request_follow | 2025-05-31 15:00:00 | 1 | 3 |
| request_follow | 2025-05-28 09:00:00 | 2 | 3 |
| follow_success | 2025-05-28 09:30:00 | 2 | 3 |
| unfollow | 2025-06-01 09:00:00 | 2 | 3 |
| follow_success | 2025-05-31 23:00:00 | 4 | 2 |
| request_follow | 2025-05-29 10:00:00 | 5 | 6 |
| follow_success | 2025-05-27 14:00:00 | 7 | 8 |
| unfollow | 2025-05-27 16:00:00 | 7 | 8 |
| follow_success | 2025-05-27 17:00:00 | 7 | 8 |
| follow_reject | 2025-05-27 18:00:00 | 7 | 8 |
Expected output
One row per day from 2025-05-27 to 2025-06-01, showing how many pairs are active at 23:59:59 of that day.
| active_connections_eod | calendar_day |
|---|---|
| 1 | 2025-05-27 |
| 2 | 2025-05-28 |
| 1 | 2025-05-29 |
| 2 | 2025-05-30 |
| 3 | 2025-05-31 |
| 2 | 2025-06-01 |
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.