Coding questions · Roblox
Write SQL for influence score and follower growth
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).
You are working on a social product with these tables:
Tables / Schemas
users
user_idBIGINT (PK)created_atTIMESTAMP
posts
post_idBIGINT (PK)author_idBIGINT (FK → users.user_id)created_atTIMESTAMP
engagement (one row per engagement event)
engagement_idBIGINT (PK)post_idBIGINT (FK → posts.post_id)viewer_idBIGINT (FK → users.user_id)event_typeSTRING (e.g.,like,comment,share)created_atTIMESTAMP
follows (directed edge)
follower_idBIGINT (FK → users.user_id)followee_idBIGINT (FK → users.user_id)created_atTIMESTAMP (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_idinfluence_score
Notes:
- Engagement “received” means engagement rows on posts where
posts.author_id = user_id. - Followers count is the number of distinct
follower_idwherefollowee_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:
monthfollowee_idnew_followers(distinct new followers gained that month)prev_month_new_followers(previous month’snew_followersusingLAG)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 windowSUM)
Assume unfollows are not tracked (so follower counts only increase).
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:
- Engagement points should be counted based on engagement.engaged_at being within the date range (regardless of when the post was created).
- New followers gained should be counted based on follows.followed_at being within the date range (where follows.followee_id is the user).
- 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.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY, NOT NULL |
| user_name | VARCHAR(50) | NOT NULL |
| created_at | DATE | NOT NULL |
Sample data:
| created_at | user_id | user_name |
|---|---|---|
| 2024-01-15 | 1 | Alice |
| 2024-02-10 | 2 | Bob |
| 2024-03-05 | 3 | Cara |
| 2024-04-22 | 4 | Dan |
| 2024-05-30 | 5 | Eve |
Table posts
| column | type | constraints |
|---|---|---|
| post_id | INT | PRIMARY KEY, NOT NULL |
| author_id | INT | NOT NULL |
| created_at | DATE | NOT NULL |
Sample data:
| author_id | created_at | post_id |
|---|---|---|
| 1 | 2025-05-05 | 101 |
| 1 | 2025-05-20 | 102 |
| 2 | 2025-05-10 | 103 |
| 3 | 2025-04-25 | 104 |
| 2 | 2025-06-02 | 105 |
Table engagement
| column | type | constraints |
|---|---|---|
| engagement_id | INT | PRIMARY KEY, NOT NULL |
| post_id | INT | NOT NULL |
| engager_id | INT | NOT NULL |
| engagement_type | VARCHAR(10) | NOT NULL |
| engaged_at | DATE | NOT NULL |
Sample data:
| engaged_at | engagement_id | engagement_type | engager_id | post_id |
|---|---|---|---|---|
| 2025-05-06 | 1 | like | 2 | 101 |
| 2025-05-06 | 2 | comment | 3 | 101 |
| 2025-05-21 | 3 | share | 4 | 102 |
| 2025-05-11 | 4 | like | 1 | 103 |
| 2025-05-12 | 5 | like | 3 | 103 |
| 2025-05-12 | 6 | comment | 4 | 103 |
| 2025-05-01 | 7 | share | 5 | 104 |
| 2025-06-03 | 8 | share | 1 | 105 |
Table follows
| column | type | constraints |
|---|---|---|
| follower_id | INT | NOT NULL |
| followee_id | INT | NOT NULL |
| followed_at | DATE | NOT NULL |
Sample data:
| followed_at | followee_id | follower_id |
|---|---|---|
| 2025-05-02 | 1 | 2 |
| 2025-05-15 | 1 | 3 |
| 2025-04-10 | 1 | 4 |
| 2025-06-01 | 1 | 5 |
| 2025-05-07 | 2 | 1 |
| 2025-05-20 | 2 | 3 |
| 2025-06-10 | 2 | 4 |
| 2025-05-05 | 3 | 5 |
| 2025-05-25 | 3 | 1 |
| 2025-04-30 | 3 | 2 |
| 2025-05-18 | 4 | 2 |
Expected output
One row per user with engagement_points (weighted sum of engagements on their posts during May 2025), new_followers (count of follows received during May 2025), and influence_score = engagement_points + new_followers.
| engagement_points | influence_score | new_followers | user_id | user_name |
|---|---|---|---|---|
| 6 | 8 | 2 | 1 | Alice |
| 4 | 6 | 2 | 2 | Bob |
| 3 | 5 | 2 | 3 | Cara |
| 0 | 1 | 1 | 4 | Dan |
| 0 | 0 | 0 | 5 | Eve |
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.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY, NOT NULL |
| user_name | VARCHAR(50) | NOT NULL |
| created_at | DATE | NOT NULL |
Sample data:
| created_at | user_id | user_name |
|---|---|---|
| 2024-01-15 | 1 | Alice |
| 2024-02-10 | 2 | Bob |
| 2024-03-05 | 3 | Cara |
| 2024-04-22 | 4 | Dan |
| 2024-05-30 | 5 | Eve |
Table follows
| column | type | constraints |
|---|---|---|
| follower_id | INT | NOT NULL |
| followee_id | INT | NOT NULL |
| followed_at | DATE | NOT NULL |
Sample data:
| followed_at | followee_id | follower_id |
|---|---|---|
| 2025-05-02 | 1 | 2 |
| 2025-05-15 | 1 | 3 |
| 2025-04-10 | 1 | 4 |
| 2025-06-01 | 1 | 5 |
| 2025-05-07 | 2 | 1 |
| 2025-05-20 | 2 | 3 |
| 2025-06-10 | 2 | 4 |
| 2025-05-05 | 3 | 5 |
| 2025-05-25 | 3 | 1 |
| 2025-04-30 | 3 | 2 |
| 2025-05-18 | 4 | 2 |
Expected output
Rows per followee per month (only months with at least one follow), with LAG-based previous month value and running cumulative sum.
| cumulative_followers | mom_change | month_start | new_followers | prev_month_new_followers | user_id | user_name |
|---|---|---|---|---|---|---|
| 1 | NULL | 2025-04-01 | 1 | NULL | 1 | Alice |
| 3 | 1 | 2025-05-01 | 2 | 1 | 1 | Alice |
| 4 | -1 | 2025-06-01 | 1 | 2 | 1 | Alice |
| 2 | NULL | 2025-05-01 | 2 | NULL | 2 | Bob |
| 3 | -1 | 2025-06-01 | 1 | 2 | 2 | Bob |
| 1 | NULL | 2025-04-01 | 1 | NULL | 3 | Cara |
| 3 | 1 | 2025-05-01 | 2 | 1 | 3 | Cara |
| 1 | NULL | 2025-05-01 | 1 | NULL | 4 | Dan |
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.