Write SQL for influence score and follower growth

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).