Coding questions · Meta
Count unconnected posts and reactions
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 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.
relationshipdescribes 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_typecan be'like'or'comment'.view_idjoins toposts.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:
-
Count how many feed impressions were for unconnected content in the last 7 days.
- Output column:
unconnected_post_count
- Output column:
-
For feed impressions in the last 7 days, compute the average number of reactions (
like+comment) per impression forfriendandunconnectedcontent.- Include impressions with zero reactions.
- Output columns:
relationship,avg_reactions_per_post
Count unconnected posts and reactions
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.
- One row for the count of
'unconnected'impressions in the window:metric = 'unconnected_post_count',relationship = NULL,avg_reactions_per_post = NULL, andunconnected_post_count= the count. - 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, andunconnected_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).
Table posts
| column | type | constraints |
|---|---|---|
| post_id | BIGINT | |
| view_id | BIGINT | |
| relationship | VARCHAR | |
| post_date | DATE |
Sample data:
| post_date | post_id | relationship | view_id |
|---|---|---|---|
| 2026-05-01 | 1001 | unconnected | 5001 |
| 2026-05-02 | 1002 | friend | 5002 |
| 2026-05-03 | 1003 | unconnected | 5003 |
| 2026-05-05 | 1004 | unconnected | 5004 |
| 2026-05-06 | 1005 | friend | 5005 |
| 2026-05-07 | 1006 | friend | 5006 |
| 2026-04-30 | 999 | unconnected | 4999 |
| 2026-04-28 | 1007 | friend | 5007 |
Table interactions
| column | type | constraints |
|---|---|---|
| interaction_id | BIGINT | |
| view_id | BIGINT | |
| interaction_type | VARCHAR | |
| interaction_ts | TIMESTAMP |
Sample data:
| interaction_id | interaction_ts | interaction_type | view_id |
|---|---|---|---|
| 7001 | 2026-05-01 12:00:00 | like | 5001 |
| 7002 | 2026-05-01 13:00:00 | comment | 5001 |
| 7003 | 2026-05-03 08:30:00 | like | 5003 |
| 7004 | 2026-05-06 10:15:00 | comment | 5005 |
| 7005 | 2026-05-06 10:16:00 | comment | 5005 |
| 7006 | 2026-05-06 11:00:00 | like | 5005 |
| 7007 | 2026-05-07 09:00:00 | comment | 5006 |
| 7008 | 2026-04-30 09:00:00 | like | 4999 |
| 7009 | 2026-04-28 09:00:00 | like | 5007 |
| 7010 | 2026-04-28 12:00:00 | comment | 5007 |
Expected output
Three rows: the unconnected impression count (3) in the 2026-05-01..2026-05-07 window, then friend avg reactions/impression (1.333) and unconnected avg (1.000).
| avg_reactions_per_post | metric | relationship | unconnected_post_count |
|---|---|---|---|
| NULL | unconnected_post_count | NULL | 3 |
| 1.333 | avg_reactions_per_post | friend | NULL |
| 1.000 | avg_reactions_per_post | unconnected | NULL |
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).
- Stack the count row and the per-relationship average rows with UNION ALL, casting the NULL columns so both branches have matching types, and add an explicit ORDER BY for determinism.