Compute unconnected 60s posts and reactions averages
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Given these tables and sample data, write SQL that answers both tasks below. Use today = 2025-09-01 and interpret "last/past 7 days" as the inclusive window 2025-08-26 through 2025-09-01. Schemas:
info_stream_views
- post_id BIGINT -- ID of post
- viewer_id BIGINT -- ID of user who viewed the post
- relationship STRING -- {Friend, Followee, Unconnected}
- duration DOUBLE -- seconds watched for that view event
- ds STRING -- 'YYYY-MM-DD' event date
post_reactions
- post_id INTEGER
- viewer_id INTEGER
- post_action STRING -- {like, comment, reshare}
- ds STRING -- 'YYYY-MM-DD'
Sample rows (small, illustrative):
info_stream_views
post_id | viewer_id | relationship | duration | ds
1 | 11 | Unconnected | 70.0 | 2025-08-31
1 | 10 | Friend | 45.0 | 2025-08-31
2 | 12 | Unconnected | 30.0 | 2025-08-30
2 | 13 | Friend | 120.5 | 2025-08-30
3 | 14 | Followee | 61.0 | 2025-08-26
3 | 11 | Unconnected | 62.0 | 2025-08-27
post_reactions
post_id | viewer_id | post_action | ds
1 | 11 | like | 2025-08-31
1 | 11 | comment | 2025-08-31
2 | 12 | like | 2025-08-30
2 | 13 | reshare | 2025-08-30
3 | 11 | like | 2025-08-27
Task A (distinct-post count): Return the number of distinct post_id that have at least one Unconnected view with duration > 60 seconds within 2025-08-26..2025-09-01 (inclusive). If multiple view rows exist for the same (post_id, viewer_id, ds), deduplicate by taking MAX(duration) for that key before applying the > 60s filter.
Task B (friend vs unconnected averages): Compute the average number of reactions per post attributable to Friend vs Unconnected viewers within the same 7-day window. Classify each reaction by joining post_reactions to info_stream_views on (post_id, viewer_id, ds); if multiple matches exist for a given (post_id, viewer_id, ds), prefer the row with the greatest duration. Count all reaction types equally (each = 1). For each relationship group in {Friend, Unconnected}, define the denominator as the number of distinct post_id that appeared in info_stream_views in the window; posts with zero reactions in a group should contribute 0 to that group's average. Return two rows: relationship, avg_reactions_per_post.
Edge cases to handle explicitly: mixed relationships across days, multiple views per user per post per day, reactions without a matching view (exclude these), and ds stored as STRING.
Overview: This question evaluates data manipulation skills in SQL and Python, focusing on deduplication, join logic between event and reaction tables, date handling for string-typed dates, and aggregation to compute per-post metrics.
Count posts with >60s Unconnected views in a 7-day window
You are given two tables, info_stream_views and post_reactions, that track post views and reactions over time. Using the schema and sample data below, write a SQL query to answer the following:
Within the date range 2025-08-26 through 2025-09-01 (inclusive), return the number of distinct post_id values that have at least one Unconnected view with duration > 60 seconds.
Important details:
- The ds column is stored as a string in 'YYYY-MM-DD' format.
- Before applying the duration > 60 seconds filter, you must deduplicate info_stream_views by (post_id, viewer_id, ds). If multiple view rows exist for the same (post_id, viewer_id, ds), keep only the row with the greatest duration (and its associated relationship).
- Only Unconnected views should be considered for this count.
Return a single row with a single column named unconnected_60s_post_count.
Tables
info_stream_views(post_id BIGINT, viewer_id BIGINT, relationship VARCHAR(20), duration DOUBLE, ds VARCHAR(10))
post_reactions(post_id BIGINT, viewer_id BIGINT, post_action VARCHAR(20), ds VARCHAR(10))
Hints
- First deduplicate info_stream_views by (post_id, viewer_id, ds) using a window function that keeps the row with the maximum duration.
- After deduplication, filter to Unconnected rows with duration > 60 seconds within the date range and count distinct post_id.
Average reactions per post for Friend vs Unconnected viewers
Write a PostgreSQL query. Using the same info_stream_views and post_reactions tables and sample data, compute the average number of reactions per post attributable to Friend vs Unconnected viewers within the date range 2025-08-26 through 2025-09-01 (inclusive).
Requirements:
1. Deduplicate views:
- In info_stream_views, multiple rows can exist for the same (post_id, viewer_id, ds).
- For each (post_id, viewer_id, ds), keep only the row with the greatest duration and use its relationship value.
2. Classify reactions:
- Join post_reactions to the deduplicated info_stream_views on (post_id, viewer_id, ds).
- If a reaction has no matching view in info_stream_views for that (post_id, viewer_id, ds), exclude that reaction entirely.
- Use the relationship from the matched view to classify each reaction as Friend or Unconnected; ignore other relationship types.
- Count all reaction types equally (each reaction counts as 1).
3. Date filtering:
- Only consider rows where ds is between '2025-08-26' and '2025-09-01' inclusive in both tables.
4. Denominator definition:
- For each relationship group in {Friend, Unconnected}, the denominator is the number of distinct post_id that appeared in info_stream_views (any relationship) within the date range.
- Posts with zero reactions in a group should still be included in that group's denominator, effectively contributing 0 to the average.
Output:
- Return exactly two rows, one for each relationship in {Friend, Unconnected}.
- Columns: relationship, avg_reactions_per_post.
Tables
info_stream_views(post_id BIGINT, viewer_id BIGINT, relationship VARCHAR(20), duration DOUBLE PRECISION, ds VARCHAR(10))
post_reactions(post_id BIGINT, viewer_id BIGINT, post_action VARCHAR(20), ds VARCHAR(10))
Hints
- First deduplicate info_stream_views by (post_id, viewer_id, ds) using a window function, then join reactions on that deduplicated set.
- Compute total reactions per relationship, compute the distinct post count from the views as a separate denominator, and divide; use a small relationships helper table or VALUES list to ensure both Friend and Unconnected appear even if one has zero reactions.