Write SQL for active follow connections

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Onsite rounds at Meta.

Q: What role is this question designed for?

This question is commonly asked for Data Engineer candidates at Meta during technical 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:
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.

PracHub · Accepted Answer

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.

Quick Overview

Write SQL for active follow connections