Write SQL for influence score and follower growth
Company: Roblox
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
You are working on a social product with these tables:
### Tables / Schemas
**users**
- `user_id` BIGINT (PK)
- `created_at` TIMESTAMP
**posts**
- `post_id` BIGINT (PK)
- `author_id` BIGINT (FK → users.user_id)
- `created_at` TIMESTAMP
**engagement** (one row per engagement event)
- `engagement_id` BIGINT (PK)
- `post_id` BIGINT (FK → posts.post_id)
- `viewer_id` BIGINT (FK → users.user_id)
- `event_type` STRING (e.g., `like`, `comment`, `share`)
- `created_at` TIMESTAMP
**follows** (directed edge)
- `follower_id` BIGINT (FK → users.user_id)
- `followee_id` BIGINT (FK → users.user_id)
- `created_at` TIMESTAMP (when the follow happened)
Assume timestamps are in UTC, and “month” means `DATE_TRUNC('month', created_at)`.
---
## 1) Influence score
Define a user’s **influence score** as:
\[
\text{influence_score}(u) = \#\text{engagement events received on u's posts} + \#\text{followers of u}
\]
Write a SQL query returning:
- `user_id`
- `influence_score`
Notes:
- Engagement “received” means engagement rows on posts where `posts.author_id = user_id`.
- Followers count is the number of distinct `follower_id` where `followee_id = user_id`.
---
## 2) Monthly follower growth
For each user (as the followee), compute monthly follower growth metrics.
Write a SQL query that outputs, for each `followee_id` and month:
- `month`
- `followee_id`
- `new_followers` (distinct new followers gained that month)
- `prev_month_new_followers` (previous month’s `new_followers` using `LAG`)
- `mom_growth` (month-over-month growth rate: `(new_followers - prev_month_new_followers) / prev_month_new_followers`, handling divide-by-zero)
- `cumulative_followers` (running total across months using a window `SUM`)
Assume unfollows are not tracked (so follower counts only increase).
Overview: This question evaluates SQL data manipulation competencies such as joins, aggregations, distinct counts, window functions, and time-based grouping for computing an influence score (engagements on a user's posts plus follower count) and monthly follower growth, and it falls under Data Manipulation (SQL/Python).
Influence Score from Engagement + New Followers (May 2025)
You are given four tables: users, posts, engagement, and follows.
Define a user's "influence_score" for the date range 2025-05-01 to 2025-05-31 (inclusive) as:
influence_score = (sum of engagement points on the user's posts during the date range) + (number of new followers the user gained during the date range)
Engagement points are weighted by engagement_type:
- 'like' = 1 point
- 'comment' = 2 points
- 'share' = 3 points
Requirements:
1) Engagement points should be counted based on engagement.engaged_at being within the date range (regardless of when the post was created).
2) New followers gained should be counted based on follows.followed_at being within the date range (where follows.followee_id is the user).
3) Return all users, even if they have 0 engagement points and/or 0 new followers.
Output columns:
- user_id
- user_name
- engagement_points
- new_followers
- influence_score
Order by influence_score DESC, then user_id ASC.
Tables
users(user_id INT, user_name VARCHAR(50), created_at DATE)
posts(post_id INT, author_id INT, created_at DATE)
engagement(engagement_id INT, post_id INT, engager_id INT, engagement_type VARCHAR(10), engaged_at DATE)
follows(follower_id INT, followee_id INT, followed_at DATE)
Hints
- Compute engagement points per author by joining posts to engagement and applying a CASE weight.
- Compute new followers per followee from follows, then LEFT JOIN both aggregates to users and COALESCE to 0.
Monthly Follower Growth with LAG and Running SUM
Using the follows table and the users table, calculate follower growth per month for each user who received at least one new follower between 2025-04-01 and 2025-06-30 (inclusive).
For each followee and month, compute:
- month_start: the first day of the month (e.g., 2025-05-01)
- new_followers: number of new follow events in that month
- prev_month_new_followers: previous month's new_followers for that followee (use LAG)
- mom_change: new_followers - prev_month_new_followers (NULL if no previous month row)
- cumulative_followers: running total of new_followers over time for that followee (use a window SUM)
Only include (followee, month) combinations that have at least one follow event in that month.
Order by user_id ASC, month_start ASC.
Tables
users(user_id INT, user_name VARCHAR(50), created_at DATE)
follows(follower_id INT, followee_id INT, followed_at DATE)
Hints
- First aggregate follows into (followee_id, month_start) with COUNT(*).
- Use LAG for previous month and a running SUM window for the cumulative total.