Count unconnected posts and reactions
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You are analyzing a newly launched feed feature intended to improve engagement by showing more unconnected content.
Assume the following tables:
- `posts(post_id BIGINT, view_id BIGINT, relationship VARCHAR, post_date DATE)`
- Each row represents one post impression in a user's feed.
- `relationship` describes the relationship between the viewer and the post author.
- Relevant values include `'friend'` and `'unconnected'`.
- `interactions(interaction_id BIGINT, view_id BIGINT, interaction_type VARCHAR, interaction_ts TIMESTAMP)`
- Each row represents one interaction on a feed impression.
- `interaction_type` can be `'like'` or `'comment'`.
- `view_id` joins to `posts.view_id`.
Assume all dates and timestamps are in UTC. Interpret "last 7 days" as the inclusive window from `CURRENT_DATE - INTERVAL '6 day'` through `CURRENT_DATE`.
Write SQL for the following:
1. Count how many feed impressions were for unconnected content in the last 7 days.
- Output column: `unconnected_post_count`
2. For feed impressions in the last 7 days, compute the average number of reactions (`like` + `comment`) per impression for `friend` and `unconnected` content.
- Include impressions with zero reactions.
- Output columns: `relationship`, `avg_reactions_per_post`
Quick Answer: This question evaluates proficiency in SQL data manipulation—specifically joins, aggregations, time-window filtering, and counting/averaging event-driven metrics—within the Data Manipulation (SQL/Python) domain.