Meta: Instagram Follow-Backs Within a Week — SQL Problem
An Instagram growth analyst studies reciprocity: when someone new follows an account, how often does the account follow them back soon after?
- Difficulty: Medium
- Topics: Joins, Dates, Aggregation
- Dialect: MySQL
- Problem: #762
Problem statement
An Instagram growth analyst studies reciprocity: when someone new follows an account, how often does the account follow them back soon after?
For every account that gained at least one follower in March 2025 (a follow with followed_at in March), return account_id, new_followers (its March follows) and followed_back — how many of those followers it followed back after being followed and within 7 days (exactly 7 days still counts). If the account already followed that person before (or at the same moment), it is not a follow-back. Return the rows in any order.
A practice scenario set at Meta: the tables and data are invented, and this is not a question from, or endorsed by, Meta.
Tables
Table: IgFollow
| Column | Type |
|---|---|
| follower_id | int |
| followee_id | int |
| followed_at | datetime |
Primary key: follower_id, followee_id.
follower_id started following followee_id at followed_at; one row per ordered pair of accounts.
Examples
Example 1
IgFollow
| follower_id | followee_id | followed_at |
|---|---|---|
| 11 | 10 | 2025-03-02 10:00:00 |
| 10 | 11 | 2025-03-05 09:00:00 |
| 12 | 10 | 2025-03-10 08:00:00 |
| 10 | 12 | 2025-03-17 08:00:00 |
| 13 | 10 | 2025-03-28 12:00:00 |
| 10 | 13 | 2025-04-05 12:00:00 |
| 14 | 15 | 2025-02-27 09:00:00 |
| 15 | 14 | 2025-03-01 00:00:00 |
| 16 | 10 | 2025-03-31 23:59:00 |
Output
| account_id | new_followers | followed_back |
|---|---|---|
| 10 | 4 | 2 |
| 11 | 1 | 0 |
| 12 | 1 | 0 |
| 14 | 1 | 0 |
How to solve Meta: Instagram Follow-Backs Within a Week
A follow-back is the reverse edge of a follow, so the query self-joins IgFollow: for a follow f (follower → account), the candidate b has the ids swapped. The timing rules — strictly after, at most seven days after — go into the join condition too.
They must be in the ON clause of a LEFT JOIN, not in WHERE: each March follow keeps its row whether or not a qualifying follow-back exists, so COUNT(*) counts every new follower while COUNT(b.follower_id) counts only the rows where the reverse edge matched. Since there is at most one row per ordered pair, nothing is double-counted. A reverse follow that happened earlier, or at the same instant, fails b.followed_at > f.followed_at — the account was following them already. Only the original follow is restricted to March, so a follow-back on 4 April for a follow on 28 March still counts.
The alternative counts with a correlated EXISTS per follow and states the window as a seconds difference BETWEEN 1 AND 604800. With the primary key on (follower, followee) each reverse lookup is a single index probe.
Reference solution (MySQL)
SELECT f.followee_id AS account_id,
COUNT(*) AS new_followers,
COUNT(b.follower_id) AS followed_back
FROM IgFollow f
LEFT JOIN IgFollow b ON b.follower_id = f.followee_id
AND b.followee_id = f.follower_id
AND b.followed_at > f.followed_at
AND b.followed_at <= DATE_ADD(f.followed_at, INTERVAL 7 DAY)
WHERE f.followed_at >= '2025-03-01' AND f.followed_at < '2025-04-01'
GROUP BY f.followee_idAnother way
SELECT f.followee_id AS account_id, COUNT(*) AS new_followers,
SUM(CASE WHEN EXISTS (SELECT 1 FROM IgFollow b WHERE b.follower_id = f.followee_id AND b.followee_id = f.follower_id
AND TIMESTAMPDIFF(SECOND, f.followed_at, b.followed_at) BETWEEN 1 AND 7 * 86400) THEN 1 ELSE 0 END) AS followed_back
FROM IgFollow f
WHERE YEAR(f.followed_at) = 2025 AND MONTH(f.followed_at) = 3
GROUP BY f.followee_id← Meta: Moderators Overturned More Often Than Their Team · Meta: Messenger Call Drops by Major App Version →