Write SQL for active follow connections
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.
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.