Meta: Instagram Follow-Backs Within a Week

Medium
762.

Meta: Instagram Follow-Backs Within a Week

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

ColumnType
follower_idint
followee_idint
followed_atdatetime

Primary key (together): follower_id, followee_id.

follower_id started following followee_id at followed_at; one row per ordered pair of accounts.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

IgFollow

follower_idfollowee_idfollowed_at
11102025-03-02 10:00:00
10112025-03-05 09:00:00
12102025-03-10 08:00:00
10122025-03-17 08:00:00
13102025-03-28 12:00:00
10132025-04-05 12:00:00
14152025-02-27 09:00:00
15142025-03-01 00:00:00
16102025-03-31 23:59:00

Output

account_idnew_followersfollowed_back
1042
1110
1210
1410
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
follower_idint
followee_idint
followed_atdatetime

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_idfollowee_idfollowed_at
11102025-03-02 10:00:00
10112025-03-05 09:00:00
12102025-03-10 08:00:00
10122025-03-17 08:00:00
13102025-03-28 12:00:00
10132025-04-05 12:00:00
14152025-02-27 09:00:00
15142025-03-01 00:00:00
16102025-03-31 23:59:00

Output

account_idnew_followersfollowed_back
1042
1110
1210
1410

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_id

Another 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 →