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`
Overview: 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.
You are analyzing Facebook feed-impression data. Each row in **`posts`** represents one impression of a piece of content shown to a viewer (identified by `view_id`), tagged by `relationship` (`'friend'` or `'unconnected'`) and the `post_date` it was shown. The **`interactions`** table records each reaction event tied to a `view_id`, with `interaction_type` being `'like'`, `'comment'`, or other types.
Restrict the analysis to the **7-day window 2026-05-01 through 2026-05-07 inclusive** (`post_date BETWEEN DATE '2026-05-01' AND DATE '2026-05-07'`).
Return a single combined result set with these four columns: `metric`, `relationship`, `avg_reactions_per_post`, `unconnected_post_count`.
1. **One row** for the count of `'unconnected'` impressions in the window: `metric = 'unconnected_post_count'`, `relationship = NULL`, `avg_reactions_per_post = NULL`, and `unconnected_post_count` = the count.
2. **One row per relationship** (`'friend'` and `'unconnected'`) for the average number of reactions per impression, where a reaction is any interaction of type `'like'` or `'comment'`. Include impressions with **zero** reactions in the average (treat a missing reaction count as 0). For these rows: `metric = 'avg_reactions_per_post'`, `relationship` = the relationship, `avg_reactions_per_post` = the average rounded to 3 decimals, and `unconnected_post_count = NULL`.
**Sort order:** the `'unconnected_post_count'` row first, then the `'avg_reactions_per_post'` rows ordered by `relationship` ascending (`friend` before `unconnected`).
Tables
posts(post_id BIGINT, view_id BIGINT, relationship VARCHAR, post_date DATE)
interactions(interaction_id BIGINT, view_id BIGINT, interaction_type VARCHAR, interaction_ts TIMESTAMP)
Hints
- Build the 7-day window once as a CTE filtering posts on post_date BETWEEN DATE '2026-05-01' AND DATE '2026-05-07', then reuse it for both metrics.
- Aggregate interactions to one reaction count per view_id using COUNT(*) FILTER (WHERE interaction_type IN ('like','comment')), then LEFT JOIN so zero-reaction impressions still count (COALESCE to 0).
Community answers
Answer by SS
For(2):
WITH filtered_list AS (
SELECT view_id, relationship
FROM posts
WHERE post_date >= CURRENT_DATE - INTERVAL '6 day'
AND post_date <= CURRENT_DATE
),
filtered_reactions AS (
SELECT view_id, COUNT(*) as total_reactions
FROM interactions
WHERE view_id IN (SELECT DISTINCT view_id FROM filtered_list)
GROUP BY view_id
)
SELECT a.relationship,
AVG(COALESCE(b.total_reactions, 0)) as avg_reactions_per_post
FROM filtered_list a
LEFT JOIN filtered_reactions b
ON a.view_id = b.view_id
GROUP BY a.relationship;
Answer by SS
For(1): SELECT COUNT(*) AS unconnected_post_count
FROM posts
WHERE relationship = 'unconnected'
AND post_date >= CURRENT_DATE - INTERVAL '6 day'
AND post_date <= CURRENT_DATE;